ballpark time for shrinkfile

After completing some intensive DB maintenance I would like to shrink a 400G
B
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an ide
a
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:

> After completing some intensive DB maintenance I would like to shrink a 40
0GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an i
dea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 40
0GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J

ballpark time for shrinkfile

After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
ThanksBased on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J

ballpark time for shrinkfile

After completing some intensive DB maintenance I would like to shrink a 400GB
database with 200GB free space. I am looking for a ballpark figure for how
long something like that would take on a standard 2CPU 8MB RAM server. If
anyone else has done something similar how long has it taken? I understand
that shrinkfile depends on a lot of factors - I am mainly looking for an idea
- will it take a day? a weekend? a week?
Thanks
Based on my experience it will take couple of tens minutes up to couple of
hours,
depends on hardware, current utilization, etc.
Regards.
"J Jetson" wrote:

> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an idea
> - will it take a day? a weekend? a week?
> Thanks
|||Hi,
My 8 CPU , 8GB RAM machine took me 15 minutes to shrink from 600 GB 290 GB.
But I recommend you to shrink in
pieces of 5000 MB each.
Thanks
Hari
SQLQ Server MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||many people would not recommend shrinking at all unless you have a very good
reason to do so.
if you're running out of diskspace, consider buying additional disks if you
can as opposed to shrinking the DB.
Greg Jackson
PDX, Oregon
|||You said yourself it depends on a lot of factors so it is very hard to give
you an accurate answer, even a ballpark. You stated that you just did some
extensive maintenance so I assume this was mainly reindexing. Reindexing
will defragment your indexes but shrinking the file will just fragment them
again. So you will undo most of what you spent all that time working on.
The DB needs plenty of free space to operate normally. While 200GB of free
space on a 200GB db may be more than you actually need it doesn't hurt to
have extra free space. Have a look at this:
http://www.microsoft.com/technet/pro.../ss2kidbp.mspx
Index Defrag
Andrew J. Kelly SQL MVP
"J Jetson" <JJetson@.discussions.microsoft.com> wrote in message
news:8AFEB9BA-C041-4E48-9A04-D4632792A202@.microsoft.com...
> After completing some intensive DB maintenance I would like to shrink a
> 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken? I understand
> that shrinkfile depends on a lot of factors - I am mainly looking for an
> idea
> - will it take a day? a weekend? a week?
> Thanks
|||"J Jetson" wrote:
> After completing some intensive DB maintenance I would like to shrink a 400GB
> database with 200GB free space. I am looking for a ballpark figure for how
> long something like that would take on a standard 2CPU 8MB RAM server. If
> anyone else has done something similar how long has it taken?
I'll chime in by saying that it's really variable. For example, my 4CPU 4GB
RAM server recently took a day to shrink an 80GB file down to 50GB.
One gotcha: it is possible for a SHRINKFILE to deadlock on a SELECT. In
that case,
the SHRINKFILE will be terminated in favor of the SELECT, and you'll have to
start
the SHRINKFILE again. So I'd recommend shrinking when the database is
relatively unused.
-- J

BAFFLED! Why won't this code work?!?!

Hello,
The below code is intended to create a sp, then loop through a set of
values, each time running the sp with the new value. The CREATE PROC
executes fine, but I get an error when attempting the loop: "Invalid
column name 'tblIR_LB_2'."
Your help appreciated!
CREATE PROC sp_CreateIRTables ( @.tbl smallint)
AS
DECLARE @.SQL varchar(999)
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
[dteDate] [smalldatetime] NULL ,
[sglSTDEV(upper)] [float] NULL ,
[sglSTDEV(lower)] [float] NULL ,
[intMA] [smallint] NULL ,
[dblPL] [float] NULL ,
[dblSTDEV] [float] NULL ,
[dblIR] [float] NULL ,
[intTotalDays] [int] NULL ,
[intExposure] [int] NULL ) ON [PRIMARY]'
EXEC (@.SQL)
DECLARE @.i smallint
SET @.i = 1
WHILE (@.i <= 32)
BEGIN
EXECUTE sp_CreateIRTables @.i
SET @.i = @.i + 1
END> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
DON'T USE sp_ PREFIX!

> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
Is it @.tbl or @.tblname? Pick one.|||> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
@.tblname is, for some reason, a SMALLINT, not any kind of string.
First, I suggest not using this kind of unmanageable scheme.
But to solve your issue in the short term, you can convert to a character
type, e.g.
SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + CONVERT(VARCHAR(12), @.tblname)
+ '] (|||You can't have a variable in the table name of a CREATE TABLE unless you use
dynamic SQL to create
the table. Why not create a temp table instead?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Butaambala" <brogers75@.yahoo.com> wrote in message
news:1131645127.077192.239150@.f14g2000cwb.googlegroups.com...
> Hello,
> The below code is intended to create a sp, then loop through a set of
> values, each time running the sp with the new value. The CREATE PROC
> executes fine, but I get an error when attempting the loop: "Invalid
> column name 'tblIR_LB_2'."
> Your help appreciated!
>
> CREATE PROC sp_CreateIRTables ( @.tbl smallint)
> AS
> DECLARE @.SQL varchar(999)
> SET @.SQL = 'CREATE TABLE [dbo].[tblIR_LB_' + @.tblname + '] (
> [dteDate] [smalldatetime] NULL ,
> [sglSTDEV(upper)] [float] NULL ,
> [sglSTDEV(lower)] [float] NULL ,
> [intMA] [smallint] NULL ,
> [dblPL] [float] NULL ,
> [dblSTDEV] [float] NULL ,
> [dblIR] [float] NULL ,
> [intTotalDays] [int] NULL ,
> [intExposure] [int] NULL ) ON [PRIMARY]'
> EXEC (@.SQL)
>
> DECLARE @.i smallint
> SET @.i = 1
> WHILE (@.i <= 32)
> BEGIN
> EXECUTE sp_CreateIRTables @.i
> SET @.i = @.i + 1
> END
>

bad querry - with very slow response

Can someone please tell me why this code is returning results after too much
time and can you please tell me how I can fix it? Thank you for your help.
SELECT * FROM CASE WHERE
LTRIM(RTRIM(DLASTNAME)) like 'rumfield%'
AND LTRIM(RTRIM(FIRSTNAME)) like 'nunu%' order by C_codes
Jamesasked and answered...

bad performance, help needed

Hi,
We have a working application that has been ok for some time but recently as
our data and users increased we started to get timeouts as some queries
take more than minute to execute. This happens only occasionally on a very
specific query that joins couple of big tables.
Now the queries are fairly complex and unoptimized but that is not what
bothers me. (also changing queries didn't help, may be something to do with
amounts of data involved?)
If I run the same query as SA it runs in a second. If I run it as a normal
user that has been given select permissions on the relevant tables it takes
ages. Are there any settings for normal users regarding limits on resource
or something like that?
We only support two users SA and TheUser as authentication is done server
side and everything is done through stored procedures spare the read
queries. TheUser is granted permissions to read some tables and to execute
the stored procedures nothing else.
Using the SQL Trace thing... I can see that the offending query does approx
350000 reads and 20 writes. I don't know what that means but thought it
might help you help me ;)
Thank you,
YordanIt is impossible to say without actually seeing the query and the DDL for
the tables involved but it sounds liek it is not using an index and scanning
the table at least once, probably more. Can you post the other info?
Andrew J. Kelly SQL MVP
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan,
Are you logging into Query Analyzer using SA and TheUser to make the
comparisons in time? Same T-SQL code? Are there any differences in the
execution plans chosen?
HTH
Jerry
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||If timeouts occur "only occasionally", then perhaps your problem is with
blocking:
INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
http://support.microsoft.com/defaul...kb;EN-US;224453
Use SQL Server Profiler to verify that your SP is making good use of
indexes. Also, as your table gets larger, fragmentation may become an issue
that needs periodic maintenance.
How To: Use SQL Profiler
http://msdn.microsoft.com/library/d...
ethowto15.asp
Planning and Creating Indexes
http://www.microsoft.com/technet/pr...s/c0618260.mspx
Microsoft SQL Server 2000 Index Defragmentation Best Practices
http://www.microsoft.com/technet/pr...n/ss2kidbp.mspx
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Yordan...
How often do you reindex your database tables?
Yosh
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Hi,
> We have a working application that has been ok for some time but recently
> as our data and users increased we started to get timeouts as some
> queries take more than minute to execute. This happens only occasionally
> on a very specific query that joins couple of big tables.
> Now the queries are fairly complex and unoptimized but that is not what
> bothers me. (also changing queries didn't help, may be something to do
> with amounts of data involved?)
> If I run the same query as SA it runs in a second. If I run it as a normal
> user that has been given select permissions on the relevant tables it
> takes ages. Are there any settings for normal users regarding limits on
> resource or something like that?
> We only support two users SA and TheUser as authentication is done server
> side and everything is done through stored procedures spare the read
> queries. TheUser is granted permissions to read some tables and to execute
> the stored procedures nothing else.
> Using the SQL Trace thing... I can see that the offending query does
> approx 350000 reads and 20 writes. I don't know what that means but
> thought it might help you help me ;)
> Thank you,
> Yordan
>
>|||Tables are not indexed... (yeah, I know)
I'll probably index to improve but I don't understand the difference the
actual user makes.Why would it work with SA? Is there any quota on resources
spared on one user? Session? I'd like to understand that...
Also same query runs fine most of the time but occasionally is taking >
minute.
its something of the sort (the actual query is too long):
select some stuff from table1 outer left join (select * from table 2) ON
(conditions) where other conditions
table1 is approx 40,000 rows
table2 is approx 600,000 rows
-Yordan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:OQ1SjFdzFHA.2652@.TK2MSFTNGP14.phx.gbl...
> It is impossible to say without actually seeing the query and the DDL for
> the tables involved but it sounds liek it is not using an index and
> scanning the table at least once, probably more. Can you post the other
> info?
> --
> Andrew J. Kelly SQL MVP
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Thanks for the links. I'll try this tomorrow as I'm home now.
To be honest it does feel like a block because I've seen few before. Its
just I don't see how it can be this time.
Thanks,
Yordan
"JT" <someone@.microsoft.com> wrote in message
news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
> If timeouts occur "only occasionally", then perhaps your problem is with
> blocking:
> INF: Understanding and Resolving SQL Server 7.0 or 2000 Blocking Problems
> http://support.microsoft.com/defaul...kb;EN-US;224453
> Use SQL Server Profiler to verify that your SP is making good use of
> indexes. Also, as your table gets larger, fragmentation may become an
> issue that needs periodic maintenance.
> How To: Use SQL Profiler
> http://msdn.microsoft.com/library/d...enethowto15.asp
> Planning and Creating Indexes
> http://www.microsoft.com/technet/pr...s/c0618260.mspx
> Microsoft SQL Server 2000 Index Defragmentation Best Practices
> [url]http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx[/ur
l]
>
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||I make my app dump the query in text. (so T-SQL code is the same).
Then I use the Query Analyzer to log-in and execute it. Using TheUser
outcomes are identical. With SA I've tried only the Analyser.
The difference in time is obvious. The exact times I get from the server
manager - using Trace (not sure about the name).
Thanks,
Yordan
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
> Yordan,
> Are you logging into Query Analyzer using SA and TheUser to make the
> comparisons in time? Same T-SQL code? Are there any differences in the
> execution plans chosen?
> HTH
> Jerry
> "Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
> news:%234EzR%23czFHA.2312@.TK2MSFTNGP14.phx.gbl...
>|||Here is an easy way to tell if it is a blocking problem. The next time a
query seems to be hung up, then run sp_who2. If a process has another
processes's SPID listed in the [blkby] column, then it is being blocked.
"Yordan Gyurchev" <yg@.gyurchev.com> wrote in message
news:%23u6dfXdzFHA.2348@.TK2MSFTNGP15.phx.gbl...
> Thanks for the links. I'll try this tomorrow as I'm home now.
> To be honest it does feel like a block because I've seen few before. Its
> just I don't see how it can be this time.
> Thanks,
> Yordan
> "JT" <someone@.microsoft.com> wrote in message
> news:e%23daTHdzFHA.692@.TK2MSFTNGP10.phx.gbl...
>|||Is it possible that since you run the same exact query as SA the execution
plan is already cached from the app and SQL server returns the result much
faster a second time
if the app user runs the same query twice does it take a long time both time
s?
http://sqlservercode.blogspot.com/
"Yordan Gyurchev" wrote:

> I make my app dump the query in text. (so T-SQL code is the same).
> Then I use the Query Analyzer to log-in and execute it. Using TheUser
> outcomes are identical. With SA I've tried only the Analyser.
> The difference in time is obvious. The exact times I get from the server
> manager - using Trace (not sure about the name).
> Thanks,
> Yordan
>
> "Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
> news:OnLPIGdzFHA.3256@.TK2MSFTNGP09.phx.gbl...
>
>

Backward Compatiblity of Management Studio

I'm relatively new to SQL Server. I have spent some time in Enterprise Manager for our SQL Server 2000 instances. We are testing SQL Server 2005, and I found that I was able to register 2000 databases into the new SQL Server 2005 Management Studio.

My question: Why not switch immediately to SQL Server 2005 Management Studio for managing all of our old 2000 instances, as well as the new 2005 instances are they are built/upgraded/migrated?

You can certainly do that, but not everything works. For one thing, the "reports" tab in SSMS won't work against SQL 2K, because 2K doesn't contain any of the dynamic management views to make it work. SSMS also can't edit the DTS packages in SQ, or work with some of the maintance plans.

If all that's OK, by all means go ahead and use it.

Buck Woody

Backward Compatiblity of Management Studio

I'm relatively new to SQL Server. I have spent some time in Enterprise Manager for our SQL Server 2000 instances. We are testing SQL Server 2005, and I found that I was able to register 2000 databases into the new SQL Server 2005 Management Studio.

My question: Why not switch immediately to SQL Server 2005 Management Studio for managing all of our old 2000 instances, as well as the new 2005 instances are they are built/upgraded/migrated?

You can certainly do that, but not everything works. For one thing, the "reports" tab in SSMS won't work against SQL 2K, because 2K doesn't contain any of the dynamic management views to make it work. SSMS also can't edit the DTS packages in SQ, or work with some of the maintance plans.

If all that's OK, by all means go ahead and use it.

Buck Woody

Backup/Restore Tran Log

Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!
Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!
|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs

>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message[vbcol=seagreen]
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagreen]
example?
>
>.
>

Backup/Restore Tran Log

Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear
I'll try to KILL SPIDs

>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
Server\MSSQL\BACKUP\BACKUP[vbcol=seagree
n]
example?[vbcol=seagreen]
>
>.
>

Backup/Restore Tran Log

Hello Experts,
This is my first time that I work with the logic of
Transaction Log Backups/Restores seriously.
--First I made a full backup, then I backup my log in a
file:
BACKUP LOG [TEST_3]
TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH INIT, NAME = N'TEST 3 backup', NOSKIP
--then I wanted to restore this log:
declare @.FilePosition as int
SELECT @.FilePosition = position
FROM msdb..backupset
WHERE database_name = 'TEST_3'
AND type != 'F'
AND backup_set_id = (SELECT MAX(backup_set_id)
FROM msdb..backupset
WHERE database_name
= 'TEST_3')
RESTORE LOG TEST_3
FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
WITH FILE = @.FilePosition
Even though I don't have any other connection in my DB
the error message I get is:
Server: Msg 3101, Level 16, State 1, Line 11
Exclusive access could not be obtained because the
database is in use.
Server: Msg 3013, Level 16, State 1, Line 11
RESTORE LOG is terminating abnormally.
Can you please post a transaction backup/restore example?
Thanks in advance!Hi,
It seems there is some one connected to your database. Execute sp_who and
check the user connected to the database, either issue KILL SPID from the
query analyzer to kill the user connected or execute the below command just
before the restore:-
Alter database TEST_3 set single_user with rollback immediate
go
Restore command -- Execute
go
Alter database TEST_3 set multi_user
Thanks
Hari
MCDBA
"Konstantinos Michas" <anonymous@.discussions.microsoft.com> wrote in message
news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
> Hello Experts,
> This is my first time that I work with the logic of
> Transaction Log Backups/Restores seriously.
> --First I made a full backup, then I backup my log in a
> file:
> BACKUP LOG [TEST_3]
> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
> --then I wanted to restore this log:
> declare @.FilePosition as int
> SELECT @.FilePosition = position
> FROM msdb..backupset
> WHERE database_name = 'TEST_3'
> AND type != 'F'
> AND backup_set_id = (SELECT MAX(backup_set_id)
> FROM msdb..backupset
> WHERE database_name
> = 'TEST_3')
> RESTORE LOG TEST_3
> FROM DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
> WITH FILE = @.FilePosition
> Even though I don't have any other connection in my DB
> the error message I get is:
> Server: Msg 3101, Level 16, State 1, Line 11
> Exclusive access could not be obtained because the
> database is in use.
> Server: Msg 3013, Level 16, State 1, Line 11
> RESTORE LOG is terminating abnormally.
> Can you please post a transaction backup/restore example?
> Thanks in advance!|||Thanks for the reply, the error insists to appear :)
I'll try to KILL SPIDs
>--Original Message--
>Hi,
>It seems there is some one connected to your database.
Execute sp_who and
>check the user connected to the database, either issue
KILL SPID from the
>query analyzer to kill the user connected or execute
the below command just
>before the restore:-
>Alter database TEST_3 set single_user with rollback
immediate
>go
>Restore command -- Execute
>go
>Alter database TEST_3 set multi_user
>Thanks
>Hari
>MCDBA
>
>
>"Konstantinos Michas"
<anonymous@.discussions.microsoft.com> wrote in message
>news:a3c601c43512$0b5aa5b0$a501280a@.phx.gbl...
>> Hello Experts,
>> This is my first time that I work with the logic of
>> Transaction Log Backups/Restores seriously.
>> --First I made a full backup, then I backup my log in a
>> file:
>> BACKUP LOG [TEST_3]
>> TO DISK = N'D:\Microsoft SQL Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH INIT, NAME = N'TEST 3 backup', NOSKIP
>> --then I wanted to restore this log:
>> declare @.FilePosition as int
>> SELECT @.FilePosition = position
>> FROM msdb..backupset
>> WHERE database_name = 'TEST_3'
>> AND type != 'F'
>> AND backup_set_id = (SELECT MAX(backup_set_id)
>> FROM msdb..backupset
>> WHERE database_name
>> = 'TEST_3')
>> RESTORE LOG TEST_3
>> FROM DISK = N'D:\Microsoft SQL
Server\MSSQL\BACKUP\BACKUP
>> TEST_3\LOG\TEST_3_2004-05-08_17-22.bak'
>> WITH FILE = @.FilePosition
>> Even though I don't have any other connection in my DB
>> the error message I get is:
>> Server: Msg 3101, Level 16, State 1, Line 11
>> Exclusive access could not be obtained because the
>> database is in use.
>> Server: Msg 3013, Level 16, State 1, Line 11
>> RESTORE LOG is terminating abnormally.
>> Can you please post a transaction backup/restore
example?
>> Thanks in advance!
>
>.
>

backup/restore

hi,
I want to know if there is a way - other than backup and restore database (because restore takes too much time as the DB size grows) - to transfer all the data from one database on the server to another on a local SQLServer. it has to be done daily, so that the manager will always have an updated copy of the data on his machine.

Any help?
Thanks in advance

you can configure replication / log shipping / database mirroing etc. but it will impact the production server performance and it nees to be monitored/administered constantly

Madhu

Backup with time expired

hello, i'm newbie at here. Nice to be here. I wanna ask about a query to backup database with time expired. I use SQL Server 7.0, 2000, and 2005. Thanx for response...
You need to provide more information. What do you mean by "with time expired".|||I mean with "time expired" is when you set backup option with overwrite existing media, you also can set a backup expire time.
Like when you set 1 day at the option then it will not backup automatically after 1 day.
|||

Hi,

Are you writing script or using wizard? If you are writing script provide and we will review. If you are using the wizard, it's pretty self explanatory, but the older versions 7 & 2000 don't have expiration, but you can delete files older than 1 day, etc. on the "specify backup disk directory" page. How we do our backups is after the backup is created it deletes previous day's backup. If you cannot afford to lose any data then do differencials frequently (hourly) with logs every 5 minutes. Deleting the differencials every 2 hours and logs every 3 hours. All of our backups are done to disk first and then to tape. Tapes are used for a week, with the weekend being save until the next week, the month-end saved until you have a year-end and year-ends saved infinitely.

Something like that.

Books on line have the scripts: MSSQL 2000

BACKUP

Syntax

Backing up an entire database:

BACKUP DATABASE { database_name | @.database_name_var }
TO < backup_device > [ ,...n ]
[ WITH
[ BLOCKSIZE = { blocksize | @.blocksize_variable } ]
[ [ , ] DESCRIPTION = { 'text' | @.text_variable } ]
[ [ , ] DIFFERENTIAL ]
[ [ , ] EXPIREDATE = { date | @.date_var }
| RETAINDAYS = { days | @.days_var } ]
[ [ , ] PASSWORD = { password | @.password_variable } ]
[ [ , ] FORMAT | NOFORMAT ]
[ [ , ] { INIT | NOINIT } ]
[ [ , ] MEDIADESCRIPTION = { 'text' | @.text_variable } ]
[ [ , ] MEDIANAME = { media_name | @.media_name_variable } ]
[ [ , ] MEDIAPASSWORD = { mediapassword | @.mediapassword_variable } ]
[ [ , ] NAME = { backup_set_name | @.backup_set_name_var } ]
[ [ , ] { NOSKIP | SKIP } ]
[ [ , ] { NOREWIND | REWIND } ]
[ [ , ] { NOUNLOAD | UNLOAD } ]
[ [ , ] RESTART ]
[ [ , ] STATS [ = percentage ] ]
]

In the code you can see EXPIRATIONDATE, RETAINDAYS, all is described in the books on line.

Backup with log truncation

I have a database aaa and i'm taking a full backup daily at 12 Am(I'm overwriting to the existing media) and at the sama time I'm also running the script given below to truncate the transaction logs.

BACKUP LOG [aaa]
WITH TRUNCATE_ONLY

Can i add this script in my full bcak? Please help me in this issue?What's you're recovery model set to?

How are you taking the dump?|||How are you taking the dump?

Brett - This is a little personal - for me I enjoy reading the sports page.

Backup will not Restore to different Instance

I dumped a database on the Prod system and tried to restore it to the Dev
system. I have done this before, but this time the Restore sits on the
'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
last entry is "Starting up database 'dbname'. There is no indication that
the database is actually restoring.
What the heck is happening?Have you tried to kill the spid and restart the restore?
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> Log's
> last entry is "Starting up database 'dbname'. There is no indication
> that
> the database is actually restoring.
> What the heck is happening?|||Andrew,
Yes, I have. I have also cycled the Instance. When I begin the restore, the
database files are allocated but the progress window remains unchanged for a
long period of time. Alsom the instance does not appear to be using CPU or
disk. The backup was taken under 8.00.534 and the restore is under 8.00.818.
Perhaps that is the problem?
Michael
"Andrew J. Kelly" wrote:
> Have you tried to kill the spid and restart the restore?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
> >I dumped a database on the Prod system and tried to restore it to the Dev
> > system. I have done this before, but this time the Restore sits on the
> > 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> > the
> > 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> > Log's
> > last entry is "Starting up database 'dbname'. There is no indication
> > that
> > the database is actually restoring.
> >
> > What the heck is happening?
>
>|||As a side-note, the database I am trying to restore into is created prior to
beginning the install. That database shows under EM like one would expect
until the Restore begins. Then it seems to disappear from EM (running on
another machine) and does not appear in sysdatabases.
"Snake" wrote:
> I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
> last entry is "Starting up database 'dbname'. There is no indication that
> the database is actually restoring.
> What the heck is happening?|||How large are the files? It can take a while to zero out large data files.
--
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:1FC7C123-BDF5-432A-996F-79FABFBC8BE5@.microsoft.com...
> Andrew,
> Yes, I have. I have also cycled the Instance. When I begin the restore,
> the
> database files are allocated but the progress window remains unchanged for
> a
> long period of time. Alsom the instance does not appear to be using CPU or
> disk. The backup was taken under 8.00.534 and the restore is under
> 8.00.818.
> Perhaps that is the problem?
> Michael
> "Andrew J. Kelly" wrote:
>> Have you tried to kill the spid and restart the restore?
>>
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Snake" <Snake@.discussions.microsoft.com> wrote in message
>> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>> >I dumped a database on the Prod system and tried to restore it to the
>> >Dev
>> > system. I have done this before, but this time the Restore sits on the
>> > 'Restore Progress' window forever, with sqlserver using 1% cpu. EM
>> > shows
>> > the
>> > 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
>> > Log's
>> > last entry is "Starting up database 'dbname'. There is no indication
>> > that
>> > the database is actually restoring.
>> >
>> > What the heck is happening?
>>

Backup will not Restore to different Instance

I dumped a database on the Prod system and tried to restore it to the Dev
system. I have done this before, but this time the Restore sits on the
'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows th
e
'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log'
s
last entry is "Starting up database 'dbname'. There is no indication that
the database is actually restoring.
What the heck is happening?Have you tried to kill the spid and restart the restore?
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> Log's
> last entry is "Starting up database 'dbname'. There is no indication
> that
> the database is actually restoring.
> What the heck is happening?|||Andrew,
Yes, I have. I have also cycled the Instance. When I begin the restore, the
database files are allocated but the progress window remains unchanged for a
long period of time. Alsom the instance does not appear to be using CPU or
disk. The backup was taken under 8.00.534 and the restore is under 8.00.818
.
Perhaps that is the problem?
Michael
"Andrew J. Kelly" wrote:

> Have you tried to kill the spid and restart the restore?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>
>|||As a side-note, the database I am trying to restore into is created prior to
beginning the install. That database shows under EM like one would expect
until the Restore begins. Then it seems to disappear from EM (running on
another machine) and does not appear in sysdatabases.
"Snake" wrote:

> I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Lo
g's
> last entry is "Starting up database 'dbname'. There is no indication th
at
> the database is actually restoring.
> What the heck is happening?|||How large are the files? It can take a while to zero out large data files.
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:1FC7C123-BDF5-432A-996F-79FABFBC8BE5@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Yes, I have. I have also cycled the Instance. When I begin the restore,
> the
> database files are allocated but the progress window remains unchanged for
> a
> long period of time. Alsom the instance does not appear to be using CPU or
> disk. The backup was taken under 8.00.534 and the restore is under
> 8.00.818.
> Perhaps that is the problem?
> Michael
> "Andrew J. Kelly" wrote:
>

Backup will not Restore to different Instance

I dumped a database on the Prod system and tried to restore it to the Dev
system. I have done this before, but this time the Restore sits on the
'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
last entry is "Starting up database 'dbname'. There is no indication that
the database is actually restoring.
What the heck is happening?
Have you tried to kill the spid and restart the restore?
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows
> the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server
> Log's
> last entry is "Starting up database 'dbname'. There is no indication
> that
> the database is actually restoring.
> What the heck is happening?
|||Andrew,
Yes, I have. I have also cycled the Instance. When I begin the restore, the
database files are allocated but the progress window remains unchanged for a
long period of time. Alsom the instance does not appear to be using CPU or
disk. The backup was taken under 8.00.534 and the restore is under 8.00.818.
Perhaps that is the problem?
Michael
"Andrew J. Kelly" wrote:

> Have you tried to kill the spid and restart the restore?
>
> --
> Andrew J. Kelly SQL MVP
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:747DA3AB-46B4-476C-A54B-B07C5C33D9C0@.microsoft.com...
>
>
|||As a side-note, the database I am trying to restore into is created prior to
beginning the install. That database shows under EM like one would expect
until the Restore begins. Then it seems to disappear from EM (running on
another machine) and does not appear in sysdatabases.
"Snake" wrote:

> I dumped a database on the Prod system and tried to restore it to the Dev
> system. I have done this before, but this time the Restore sits on the
> 'Restore Progress' window forever, with sqlserver using 1% cpu. EM shows the
> 'Restore Database' sessions with an unchanging 422 I/O. The Sql Server Log's
> last entry is "Starting up database 'dbname'. There is no indication that
> the database is actually restoring.
> What the heck is happening?
|||How large are the files? It can take a while to zero out large data files.
Andrew J. Kelly SQL MVP
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:1FC7C123-BDF5-432A-996F-79FABFBC8BE5@.microsoft.com...[vbcol=seagreen]
> Andrew,
> Yes, I have. I have also cycled the Instance. When I begin the restore,
> the
> database files are allocated but the progress window remains unchanged for
> a
> long period of time. Alsom the instance does not appear to be using CPU or
> disk. The backup was taken under 8.00.534 and the restore is under
> 8.00.818.
> Perhaps that is the problem?
> Michael
> "Andrew J. Kelly" wrote:

Backup up to disk file

I have SQL 2000 set to back up our primary database every six hours to a
disk file. Over time, the disk file has gotten rather large. How can I
delete the some of the older content of the Backup Device? And can I make a
job to have it delete some of the older contents on a regular basis?You cannot delete some of the backups from a backup device. It is all or nothing (see the INIT and
NOINIT options of the backup command).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Stephen Ott" <sott3@.yahoo.com> wrote in message news:eQ0PIXSfDHA.2400@.TK2MSFTNGP11.phx.gbl...
> I have SQL 2000 set to back up our primary database every six hours to a
> disk file. Over time, the disk file has gotten rather large. How can I
> delete the some of the older content of the Backup Device? And can I make a
> job to have it delete some of the older contents on a regular basis?
>|||Have a look at the RETAINDAYS property of the BACKUP command in BOL
--
HTH
Ryan Waight, MCDBA, MCSE
"Stephen Ott" <sott3@.yahoo.com> wrote in message
news:eQ0PIXSfDHA.2400@.TK2MSFTNGP11.phx.gbl...
> I have SQL 2000 set to back up our primary database every six hours to a
> disk file. Over time, the disk file has gotten rather large. How can I
> delete the some of the older content of the Backup Device? And can I make
a
> job to have it delete some of the older contents on a regular basis?
>|||RATAINDAYS doesn't delete contents of a backup device. It only prohibit you from doing INIT before a
certain number of days has elapsed.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Ryan Waight" <Ryan_Waight@.nospam.hotmail.com> wrote in message
news:Ols8BaSfDHA.3104@.TK2MSFTNGP11.phx.gbl...
> Have a look at the RETAINDAYS property of the BACKUP command in BOL
> --
> HTH
> Ryan Waight, MCDBA, MCSE
> "Stephen Ott" <sott3@.yahoo.com> wrote in message
> news:eQ0PIXSfDHA.2400@.TK2MSFTNGP11.phx.gbl...
> > I have SQL 2000 set to back up our primary database every six hours to a
> > disk file. Over time, the disk file has gotten rather large. How can I
> > delete the some of the older content of the Backup Device? And can I make
> a
> > job to have it delete some of the older contents on a regular basis?
> >
> >
>

Backup transaction logs and replication simultaneously

Is there any problem with implementating some kind of replication and
backing up transaction logs at the same time? SQL Server 2000 Standard
Edition

The server is configured to back up log files regularly, with the
presence
of a(New) Disaster recovery server and with an inability for
standby(Standard Edition)I am planning to implement some kind of
replication hopefully still backing up the transaction logs to enable
a point in time recovery.

Thanks in advance for your great ideas

Vincento"Vincento Harris" <wumutek@.yahoo.com> wrote in message
news:2fa13ee7.0409281008.347cee08@.posting.google.c om...
> Is there any problem with implementating some kind of replication and
> backing up transaction logs at the same time? SQL Server 2000 Standard
> Edition
> The server is configured to back up log files regularly, with the
> presence
> of a(New) Disaster recovery server and with an inability for
> standby(Standard Edition)I am planning to implement some kind of
> replication hopefully still backing up the transaction logs to enable
> a point in time recovery.
> Thanks in advance for your great ideas
> Vincento

Check out "Backing Up and Restoring Replication Databases" in Books Online -
it covers each type of replication, as well as how to combine replication
with log shipping.

If that doesn't give you enough information, you could post in
microsoft.public.sqlserver.replication - you might get a more detailed
answer there.

Simon