How to se TempTables dat in Query Analazer

Hi all,
i am very interested on sqlprogramming, i want to see the #temptables
data in Query analazer, and i want to create a log files that how many
#tables created at instalation time , later i want to display the data
in form or grid, any one can please help me..
Thanks in advance
SureshSuresh wrote:
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>
I'm not sure that I fully understand what you are looking for but if you
want to view data in a temp table you just run a select like on every
other regular table - e.g. SELECT column1, column2... FROM #YourTempTable
Regards
Steen Schlter Persson
DBA|||Read up on temp tables within Books Online.
If you create a temp table (denoted by a single # sign) you can query the
data within the table ONLY if you query it on the same connection that
created the table (and the thing that created the table was not a stored
procedure that you just ececuted).
I am not sure what you mean when you say you want to see how many # tables
were created "at instalation time." Installation of what?
Keith Kratochvil
"Suresh" <suresh_yalla@.hotmail.com> wrote in message
news:1149678535.349104.296080@.y43g2000cwc.googlegroups.com...
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>

How to se TempTables dat in Query Analazer

Hi all,
i am very interested on sqlprogramming, i want to see the #temptables
data in Query analazer, and i want to create a log files that how many
#tables created at instalation time , later i want to display the data
in form or grid, any one can please help me..
Thanks in advance
SureshSuresh wrote:
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>
I'm not sure that I fully understand what you are looking for but if you
want to view data in a temp table you just run a select like on every
other regular table - e.g. SELECT column1, column2... FROM #YourTempTable
--
Regards
Steen Schlüter Persson
DBA|||Read up on temp tables within Books Online.
If you create a temp table (denoted by a single # sign) you can query the
data within the table ONLY if you query it on the same connection that
created the table (and the thing that created the table was not a stored
procedure that you just ececuted).
I am not sure what you mean when you say you want to see how many # tables
were created "at instalation time." Installation of what?
--
Keith Kratochvil
"Suresh" <suresh_yalla@.hotmail.com> wrote in message
news:1149678535.349104.296080@.y43g2000cwc.googlegroups.com...
> Hi all,
> i am very interested on sqlprogramming, i want to see the #temptables
> data in Query analazer, and i want to create a log files that how many
> #tables created at instalation time , later i want to display the data
> in form or grid, any one can please help me..
>
> Thanks in advance
> Suresh
>

how to run sql log shipping manually

Hi everyone

In my project, sometimes, I need to run log shipping manually rather that it runs automally when the schedule occured.

manually run log shipping means call the log shipping in code.

Thanks.

Are you using SQL2000 or SQL2005?

====================
For SQL2000, log shipping usesSqlmaint.exe to back up and to restore databases. When SQL Server creates a transaction log backup as part of a log shipping setup, Sqlmaint.exe connects to the monitor server and updates the log_shipping_primaries table with the last_backup_filename information. Similarly, when you run a Copy or a Restore job on a secondary server, Sqlmaint.exe connects to the monitor server and updates the log_shipping_secondaries table.

====================

Fro SQL2005,?after we enable and configure log shipping for the specific database in SQL Server 2005, several agent jobs are created to maintain a series of steps for log shipping. Generally, this includes a backup job for primary server, a copy and a restore job for secondary server and an alert job for monitor server.

While SQL Agent is running, the job invocation engine monitors the schedule and prepares to launch the job next time. Then, the job manager fetches the detailed steps of the active job from msdb tables and executes it. As for the backup/copy/restore jobs, they involve cmdshell subsystem to launch the external executable sqllogship.exe (SMO application) to finish the specific tasks. Sqllogship.exe will update the information in log_shipping_monitor_primary, log_shipping_monitor_secondary, log_shipping_monitor_history_detail, and log_shipping_monitor_error_detail tables on primary or secondary server. This is implemented by running some undocumented log shipping stored procedures in master database, including:

sp_MSadd_log_shipping_history_detail

sp_MSadd_log_shipping_error_detail

and directly modify those correlated columns in log_shipping_monitor_primary, log_shipping_monitor_secondary tables.

At the end of the specific task in each sqllogship.exe execution, SQL Server will issue a series of steps to synchronize the information between primary/secondary server and the monitor server. This is also implemented by running some undocumented log shipping stored procedures in master database, including:

sp_processlogshippingmonitorprimary

sp_processlogshippingmonitorsecondary

sp_processlogshippingmonitorhistory

sp_processlogshippingretentioncleanup

If the monitor server is a remote machine to the SQL box where the sqllogship.exe is running, a distributed query for these sprocs with linked server built in-house is being used to update the info on the remote monitor server.

Next, when each job is finished, the correlated job history info is recorded in the msdb tables by the SQL Agent.|||your answer is very nice, thank you very much !|||I'm glad that I can help Happy new year my friend!sql

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the D
B
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/ ****************************************
***********************************
***********
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
****************************************
************************************
**********/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased fro
m
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLSer...=12717
7
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This might be useful.
CREATE TABLE #Tables
( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT
[name],
[data]
FROM #Tables
DROP TABLE #Tables
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl
..
>I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there a
re
> no obvious reasons for the increase. We have also tried Shrinking both the
DB
> and file with little reduction in size. Is there a way to query the databa
se
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasib
le
> due to the number of objects. TIA
> Antony|||Thanks everyone for the advice. I found the table that was causing the issue
.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there ar
e
>no obvious reasons for the increase. We have also tried Shrinking both the
DB
>and file with little reduction in size. Is there a way to query the databas
e
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasibl
e
>due to the number of objects. TIA
>Antony

How to retrieve the (MB) size of all tables in a database

I have a database under SQL2005 that in a 24 hour period has increased from
200MB to 2GB (this is the dbf file size not the log file size) and there are
no obvious reasons for the increase. We have also tried Shrinking both the DB
and file with little reduction in size. Is there a way to query the database
and find out the sizes of tables etc to help with further investigation?
Looking at the properties of each in Management studio really isn't feasible
due to the number of objects. TIA
AntonyThe sp below will return all the tables in a database, by reserved size
desc (large to small)
Run this sp in the database of concern:
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROCEDURE [dbo].[BigTables]
AS
/**************************************************************************************
*
* BigTables.sql
* Bill Graziano (SQLTeam.com)
* graz@.sqlteam.com
* v1.1
*
**************************************************************************************/
declare @.id int
declare @.type character(2)
declare @.pages int
declare @.dbname sysname
declare @.dbsize dec(15,0)
declare @.bytesperpage dec(15,0)
declare @.pagesperMB dec(15,0)
create table #spt_space
(
objid int null,
rows int null,
reserved dec(15) null,
data dec(15) null,
indexp dec(15) null,
unused dec(15) null
)
set nocount on
-- Create a cursor to loop through the user tables
declare c_tables cursor for
select id
from sysobjects
where xtype = 'U'
open c_tables
fetch next from c_tables
into @.id
while @.@.fetch_status = 0
begin
/* Code from sp_spaceused */
insert into #spt_space (objid, reserved)
select objid = @.id, sum(reserved)
from sysindexes
where indid in (0, 1, 255)
and id = @.id
select @.pages = sum(dpages)
from sysindexes
where indid < 2
and id = @.id
select @.pages = @.pages + isnull(sum(used), 0)
from sysindexes
where indid = 255
and id = @.id
update #spt_space
set data = @.pages
where objid = @.id
/* index: sum(used) where indid in (0, 1, 255) - data */
update #spt_space
set indexp = (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
- data
where objid = @.id
/* unused: sum(reserved) - sum(used) where indid in (0, 1, 255) */
update #spt_space
set unused = reserved
- (select sum(used)
from sysindexes
where indid in (0, 1, 255)
and id = @.id)
where objid = @.id
update #spt_space
set rows = i.rows
from sysindexes i
where i.indid < 2
and i.id = @.id
and objid = @.id
fetch next from c_tables
into @.id
end
select --top 25
Table_Name = (select left(name,25) from sysobjects where id = objid),
rows = convert(char(11), rows),
reserved_MB = ltrim(str(reserved * d.low / 1048576.,15,0) + ' ' +
'MB'),
data_MB = ltrim(str(data * d.low / 1048576.,15,0) + ' ' + 'MB'),
index_size_MB = ltrim(str(indexp * d.low / 1048576.,15,0) + ' ' +
'MB'),
unused_MB = ltrim(str(unused * d.low / 1048576.,15,0) + ' ' + 'MB')
from #spt_space, master.dbo.spt_values d
where d.number = 1
and d.type = 'E'
order by reserved desc
drop table #spt_space
close c_tables
deallocate c_tables|||Antony wrote:
> I have a database under SQL2005 that in a 24 hour period has increased from
> 200MB to 2GB (this is the dbf file size not the log file size) and there are
> no obvious reasons for the increase. We have also tried Shrinking both the DB
> and file with little reduction in size. Is there a way to query the database
> and find out the sizes of tables etc to help with further investigation?
> Looking at the properties of each in Management studio really isn't feasible
> due to the number of objects. TIA
> Antony
There is a known bug with the auto-growth function in SQL 2005. Sounds
like that might be your problem.
http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=127177
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--=_NextPart_000_0468_01C71468.EBBF5E10
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
This might be useful.
CREATE TABLE #Tables ( [name] nvarchar(20),
[rows] char(11),
[reserved] varchar(18),
[data] varchar(18),
[index_size] varchar(18),
[unused] varchar(18)
)
EXECUTE sp_MSForEachTable 'INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT [name],
[data]
FROM #Tables
DROP TABLE #Tables
-- Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill without getting a little closer to =the top yourself.
- H. Norman Schwarzkopf
"Antony" <info AT webpc DOT biz> wrote in message =news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl...
>I have a database under SQL2005 that in a 24 hour period has increased =from
> 200MB to 2GB (this is the dbf file size not the log file size) and =there are
> no obvious reasons for the increase. We have also tried Shrinking both =the DB
> and file with little reduction in size. Is there a way to query the =database
> and find out the sizes of tables etc to help with further =investigation?
> Looking at the properties of each in Management studio really isn't =feasible
> due to the number of objects. TIA
> Antony
--=_NextPart_000_0468_01C71468.EBBF5E10
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

This might be useful.
CREATE TABLE =#Tables ( [name] nvarchar(20), [rows] char(11), [reserved] varchar(18), [data] varchar(18), [index_size] varchar(18), =[unused] varchar(18) )
EXECUTE sp_MSForEachTable ='INSERT INTO #Tables EXECUTE sp_spaceused [?]'
SELECT [name], [data]FROM #Tables
DROP TABLE #Tables
-- Arnie Rowland, =Ph.D.Westwood Consulting, Inc
Most good judgment comes from =experience. Most experience comes from bad judgment. - Anonymous
You can't help someone get up a hill =without getting a little closer to the top yourself.- H. Norman Schwarzkopf
"Antony" =wrote in message news:u0L0oEKFHHA.3212@.TK2MSFTNGP02.phx.gbl...>I =have a database under SQL2005 that in a 24 hour period has increased from> 200MB =to 2GB (this is the dbf file size not the log file size) and there are> =no obvious reasons for the increase. We have also tried Shrinking both the DB> and file with little reduction in size. Is there a way to =query the database> and find out the sizes of tables etc to help with =further investigation?> Looking at the properties of each in Management =studio really isn't feasible> due to the number of objects. TIA> Antony

---

--=_NextPart_000_0468_01C71468.EBBF5E10--|||Thanks everyone for the advice. I found the table that was causing the issue.
Antony
On 11/30/2006 12:04:10 PM, "Antony" wrote:
>I have a database under SQL2005 that in a 24 hour period has increased from
>200MB to 2GB (this is the dbf file size not the log file size) and there are
>no obvious reasons for the increase. We have also tried Shrinking both the DB
>and file with little reduction in size. Is there a way to query the database
>and find out the sizes of tables etc to help with further investigation?
>Looking at the properties of each in Management studio really isn't feasible
>due to the number of objects. TIA
>Antony

How to restore Transaction log

Hello everyone,
I have a problem which is driving me crazy. I can't figure it out how I'm
going to solve this problem but I appreciate any help in this regards.
I'm configuring a log shipping server and it's almost done. The only problem
I'm having is restoring logs from the primary server. the primary server is
appending logs in a log file. If I use the RESTORE HEADERONLY command to
read the file I can see lot's of backup sets like FILE 1, FILE 2, FILE
3....Now I know how to use the restore command but my problem is using TSQL
how can I restore this file in one go...I mean I don't have to restore
backup sets one by one...
Thanx.
Muneeb.You cannot make a single RESTORE command do several RESTORE operations. You
need to do several RESTORE executions.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Muneeb Ahmed" <muneeb.ahmed@.mhs.com> wrote in message
news:eBKYz4UDEHA.2564@.TK2MSFTNGP11.phx.gbl...
> Hello everyone,
> I have a problem which is driving me crazy. I can't figure it out how I'm
> going to solve this problem but I appreciate any help in this regards.
> I'm configuring a log shipping server and it's almost done. The only
problem
> I'm having is restoring logs from the primary server. the primary server
is
> appending logs in a log file. If I use the RESTORE HEADERONLY command to
> read the file I can see lot's of backup sets like FILE 1, FILE 2, FILE
> 3....Now I know how to use the restore command but my problem is using
TSQL
> how can I restore this file in one go...I mean I don't have to restore
> backup sets one by one...
> Thanx.
> Muneeb.
>

how to restore the sqlserver2000s mdb files to another sqlserver

Hi there,
My sqlserver 2000 was down(means failed to reload ) I just copied all the mdb files and log files from that server. now i wants to reload my databases from the mdb files. is it possible?. If so pls inform me how to do that.
Its very urgent. Pls help me:
confused:If you have all of the MDF and LDF files look into using SP_ATTACH_DB. After that you'll need to recreate the logins in MASTER from those in your databases.

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgrade
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com

How to Restore SQL2K Full bkup then tran logs?

I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
backup, then the transaction log backups to 2005. Am getting error below w/
full in standby - How to resolve? Thanks.
RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt',
REPLACE,
MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
Msg 3180, Level 16, State 1, Line 2
This backup cannot be restored using WITH STANDBY because a database upgrade
is needed. Reissue the RESTORE without WITH STANDBY.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.Chris wrote:
> I am upgrading from SQL2000 to SQL2005 - I want to restore a full SQL2000
> backup, then the transaction log backups to 2005. Am getting error below w
/
> full in standby - How to resolve? Thanks.
> RESTORE DATABASE [MyDB] FROM DISK = N'\\srvimgdb2\bkData\MyDB.bak'
> WITH FILE = 1, NOUNLOAD, STATS = 10, STANDBY = N'E:\dbData\standbyMyDB.txt
',
> REPLACE,
> MOVE N'MyDB_Data' TO N'E:\dbData\MyDB_Data.mdf',
> MOVE N'MyDB_Log' TO N'C:\dbLogs\MyDB_log.ldf'
>
> Msg 3180, Level 16, State 1, Line 2
> This backup cannot be restored using WITH STANDBY because a database upgra
de
> is needed. Reissue the RESTORE without WITH STANDBY.
> Msg 3013, Level 16, State 1, Line 2
> RESTORE DATABASE is terminating abnormally.
Try WITH NORECOVERY instead of WITH STANDBY
Tracy McKibben
MCDBA
http://www.realsqlguy.com