I have a big sql database running on sql server 2000. The manager wasn't
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
--
Thanks for any help
Paul"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
--
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>>I have a big sql database running on sql server 2000. The manager wasn't
>>watching the file sizes and the db made it up to 144 gig and the log is up
>>to 80 gig. We have been working on pruning this pig but it just has
>>completely consumed the disk. My expertise is not SQL, but I am the guy
>>for this issue.
>> So I am in a quandary. A Delete statement was run to prune the db down,
>> but that just caused the tlog to chew up the disk space that was left on
>> the disk. So things got slow so we ended up restarting the sql service
>> and figure the Checkpoint piece is running in the background rebuilding
>> the pointers.
>> Question #1)
>> Is there a way to determine if Checkpointing is going on rebuilding the
>> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
>> (EM) won't respond right now. I can manage the DB's (except for the DB
>> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
>> you gotta keep things up and running.
>> Question #2)
>> If/When I get control back I want to chuck the Log via
>> a) sp_detach_db
>> b) Delete the Log file
>> c) sp_attach_single_file_db
>> Will this work?
>>
>> Question #3)
>> There are months and months of data that is easily purgable by date but I
>> want to avoid making the log file large and clogging up the system again.
>> Can I use a statement like
>> Delete from tableA where date_time between '2006-10-01 00:00:00' and
>> '2006-10-14 23:59:59'
>> I am unsure how the SQL server will handle this statement. If there are
>> only 5% of the records in the DB that match this query will the log file
>> only grow by that amount? I assume it must for backout purposes.
>> --
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.
> --
> Thanks for any help
> Paul
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>>I have a big sql database running on sql server 2000. The manager wasn't
>>watching the file sizes and the db made it up to 144 gig and the log is up
>>to 80 gig. We have been working on pruning this pig but it just has
>>completely consumed the disk. My expertise is not SQL, but I am the guy
>>for this issue.
>> So I am in a quandary. A Delete statement was run to prune the db down,
>> but that just caused the tlog to chew up the disk space that was left on
>> the disk. So things got slow so we ended up restarting the sql service
>> and figure the Checkpoint piece is running in the background rebuilding
>> the pointers.
>> Question #1)
>> Is there a way to determine if Checkpointing is going on rebuilding the
>> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
>> (EM) won't respond right now. I can manage the DB's (except for the DB
>> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
>> you gotta keep things up and running.
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
>> Question #2)
>> If/When I get control back I want to chuck the Log via
>> a) sp_detach_db
>> b) Delete the Log file
>> c) sp_attach_single_file_db
>> Will this work?
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
>>
>> Question #3)
>> There are months and months of data that is easily purgable by date but I
>> want to avoid making the log file large and clogging up the system again.
>> Can I use a statement like
>> Delete from tableA where date_time between '2006-10-01 00:00:00' and
>> '2006-10-14 23:59:59'
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
>> I am unsure how the SQL server will handle this statement. If there are
>> only 5% of the records in the DB that match this query will the log file
>> only grow by that amount? I assume it must for backout purposes.
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
>> --
>> Thanks for any help
>> Paul
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy for
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, but
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
> won't respond right now. I can manage the DB's (except for the DB pig) via
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)
> I have a follow up but it refers to code pruning it self. I will repost
>
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Showing posts with label manager. Show all posts
Showing posts with label manager. Show all posts
Monday, March 19, 2012
Big Database needs pruning
I have a big sql database running on sql server 2000. The manager wasn't
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
Thanks for any help
Paul
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.
|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.
> --
> Thanks for any help
> Paul
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy for
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, but
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
> won't respond right now. I can manage the DB's (except for the DB pig) via
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====
Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========
field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)
> I have a follow up but it refers to code pruning it self. I will repost
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
Thanks for any help
Paul
"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.
|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.
> --
> Thanks for any help
> Paul
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>
|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy for
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, but
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
> won't respond right now. I can manage the DB's (except for the DB pig) via
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====
Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========
field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)
> I have a follow up but it refers to code pruning it self. I will repost
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Big Database needs pruning
I have a big sql database running on sql server 2000. The manager wasn't
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
Thanks for any help
Paul"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.
> --
> Thanks for any help
> Paul
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy f
or
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, b
ut
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM
)
> won't respond right now. I can manage the DB's (except for the DB pig) vi
a
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====
Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========
field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)
> I have a follow up but it refers to code pruning it self. I will repost
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
watching the file sizes and the db made it up to 144 gig and the log is up
to 80 gig. We have been working on pruning this pig but it just has
completely consumed the disk. My expertise is not SQL, but I am the guy for
this issue.
So I am in a quandary. A Delete statement was run to prune the db down, but
that just caused the tlog to chew up the disk space that was left on the
disk. So things got slow so we ended up restarting the sql service and
figure the Checkpoint piece is running in the background rebuilding the
pointers.
Question #1)
Is there a way to determine if Checkpointing is going on rebuilding the
pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM)
won't respond right now. I can manage the DB's (except for the DB pig) via
SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
keep things up and running.
Question #2)
If/When I get control back I want to chuck the Log via
a) sp_detach_db
b) Delete the Log file
c) sp_attach_single_file_db
Will this work?
Question #3)
There are months and months of data that is easily purgable by date but I
want to avoid making the log file large and clogging up the system again.
Can I use a statement like
Delete from tableA where date_time between '2006-10-01 00:00:00' and
'2006-10-14 23:59:59'
I am unsure how the SQL server will handle this statement. If there are
only 5% of the records in the DB that match this query will the log file
only grow by that amount? I assume it must for backout purposes.
Thanks for any help
Paul"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
I've always used the following statements to shrink big logs.
backup log DBName with truncate_only
go
use DBName
go
dbcc shrinkfile(2, 100, TRUNCATEONLY)
go
You should also look into backing up the log file on a regular basis or
changing to the Simple recovery model if your model allows it.|||This really isn't providing any answers to my questions. Do you know about
the Checkpoint, etc...?
Paul Bergson
MVP - Directory Services
MCT, MCSE, MCSA, Security+, BS CSci
2003, 2000 (Early Achiever), NT
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Isaac Alexander" <isaacNOSPAM@.goNOSPAMprocura.com> wrote in message
news:efbR2j8dHHA.2316@.TK2MSFTNGP04.phx.gbl...
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> I've always used the following statements to shrink big logs.
> backup log DBName with truncate_only
> go
> use DBName
> go
> dbcc shrinkfile(2, 100, TRUNCATEONLY)
> go
> You should also look into backing up the log file on a regular basis or
> changing to the Simple recovery model if your model allows it.
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
> --
> Thanks for any help
> Paul
>|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
>I have a big sql database running on sql server 2000. The manager wasn't
>watching the file sizes and the db made it up to 144 gig and the log is up
>to 80 gig. We have been working on pruning this pig but it just has
>completely consumed the disk. My expertise is not SQL, but I am the guy
>for this issue.
> So I am in a quandary. A Delete statement was run to prune the db down,
> but that just caused the tlog to chew up the disk space that was left on
> the disk. So things got slow so we ended up restarting the sql service
> and figure the Checkpoint piece is running in the background rebuilding
> the pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager
> (EM) won't respond right now. I can manage the DB's (except for the DB
> pig) via SQL Analyzer. It is amazing how quick you can learn tsql when
> you gotta keep things up and running.
Check the SQL Error log, but my guess is that DB is still in recovery mode.
(sorry, I don't recall off the top of my head the command to get this via
QA.
I THINK
select * from master.dbo.sysdatabases w
will give the info, but I don't recall which status you need to look for.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
It MIGHT. But don't do that. It's one of those, "99% of the time it'll
work.. and 1% you're hosed.)
You may want to do an actual transaction log backup (especially if up to now
you have a consistent chain of backups.)
Otherwise, do a backup log PigDB with truncate_only
Then DBCC shrinkfile on the DB log.
>
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
>
Certainly.
You can even do this in a loop with a SET ROWCOUNT and do backups of the log
in between.
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.
Unfortunately, off the top of my head, I'm not 100% sure how to calculate
this (there are ways) but I'd start with that assumption.
Hope this helps.
> --
> Thanks for any help
> Paul
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||I can't believe I didn't think to look in the logs, duh... Only 72 hours to
go, what a stinking pig
I have a follow up but it refers to code pruning it self. I will repost
Paul
http://www.pbbergs.com
Please no e-mails, any questions should be posted in the NewsGroup
This posting is provided "AS IS" with no warranties, and confers no rights.
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:%23RdOZD9dHHA.1240@.TK2MSFTNGP04.phx.gbl...
>
> "Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
> news:OD3Vvd8dHHA.2128@.TK2MSFTNGP04.phx.gbl...
> Check the SQL Error log, but my guess is that DB is still in recovery
> mode.
> (sorry, I don't recall off the top of my head the command to get this via
> QA.
> I THINK
> select * from master.dbo.sysdatabases w
> will give the info, but I don't recall which status you need to look for.
>
> It MIGHT. But don't do that. It's one of those, "99% of the time it'll
> work.. and 1% you're hosed.)
> You may want to do an actual transaction log backup (especially if up to
> now you have a consistent chain of backups.)
> Otherwise, do a backup log PigDB with truncate_only
> Then DBCC shrinkfile on the DB log.
> Certainly.
> You can even do this in a loop with a SET ROWCOUNT and do backups of the
> log in between.
>
> Unfortunately, off the top of my head, I'm not 100% sure how to calculate
> this (there are ways) but I'd start with that assumption.
>
> Hope this helps.
>
> --
> Greg Moore
> SQL Server DBA Consulting Remote and Onsite available!
> Email: sql (at) greenms.com
> http://www.greenms.com/sqlserver.html
>|||Paul Bergson [MVP-DS] wrote:
> I have a big sql database running on sql server 2000. The manager wasn't
> watching the file sizes and the db made it up to 144 gig and the log is up
> to 80 gig. We have been working on pruning this pig but it just has
> completely consumed the disk. My expertise is not SQL, but I am the guy f
or
> this issue.
> So I am in a quandary. A Delete statement was run to prune the db down, b
ut
> that just caused the tlog to chew up the disk space that was left on the
> disk. So things got slow so we ended up restarting the sql service and
> figure the Checkpoint piece is running in the background rebuilding the
> pointers.
> Question #1)
> Is there a way to determine if Checkpointing is going on rebuilding the
> pointers,etc... ? Reason for the curiosity is that Enterprise Manager (EM
)
> won't respond right now. I can manage the DB's (except for the DB pig) vi
a
> SQL Analyzer. It is amazing how quick you can learn tsql when you gotta
> keep things up and running.
From Books Online
Table master..sysdatabases:
Field [status]:
=====
Status bits, some of which can be set by the user with sp_dboption (read
only, dbo use only, single user, and so on):
1 = autoclose; set with sp_dboption.
4 = select into/bulkcopy; set with sp_dboption.
8 = trunc. log on chkpt; set with sp_dboption.
16 = torn page detection, set with sp_dboption.
32 = loading.
64 = pre recovery.
128 = recovering.
256 = not recovered.
512 = offline; set with sp_dboption.
1024 = read only; set with sp_dboption.
2048 = dbo use only; set with sp_dboption.
4096 = single user; set with sp_dboption.
32768 = emergency mode.
4194304 = autoshrink.
1073741824 = cleanly shutdown.
Multiple bits can be on at the same time.
=========
field [status2]:
16384 = ANSI null default; set with sp_dboption.
65536 = concat null yields null , set with sp_dboption.
131072 = recursive triggers, set with sp_dboption.
1048576 = default to local cursor, set with sp_dboption.
8388608 = quoted identifier, set with
sp_dboption.
33554432 = cursor close on commit, set with sp_dboption.
67108864 = ANSI nulls, set with sp_dboption.
268435456 = ANSI warnings, set with sp_dboption.
536870912 = full text enabled, set with sp_fulltext_database.
> Question #2)
> If/When I get control back I want to chuck the Log via
> a) sp_detach_db
> b) Delete the Log file
> c) sp_attach_single_file_db
> Will this work?
You better do the following:
backup log <dbname> with no_log
dbcc shrinkdatabase('<dbname>',TRUNCATEONLY)
> Question #3)
> There are months and months of data that is easily purgable by date but I
> want to avoid making the log file large and clogging up the system again.
> Can I use a statement like
> Delete from tableA where date_time between '2006-10-01 00:00:00' and
> '2006-10-14 23:59:59'
Yes
> I am unsure how the SQL server will handle this statement. If there are
> only 5% of the records in the DB that match this query will the log file
> only grow by that amount? I assume it must for backout purposes.|||"Paul Bergson [MVP-DS]" <pbergson@.allete_nospam.com> wrote in message
news:OSMpANFeHHA.1244@.TK2MSFTNGP04.phx.gbl...
>I can't believe I didn't think to look in the logs, duh... Only 72 hours
>to go, what a stinking pig
>
Yeah, I've done the same thing regarding forgetting ;-)
> I have a follow up but it refers to code pruning it self. I will repost
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html
Sunday, February 12, 2012
Best way to determine memory used?
What's the best perfmon counter for determining memory used by SQL? Is it
SQL Server: Memory Manager : Total Server Memory?Hi
If you are only interested in the total used rather than say the amount
used by the cache or query optimiser then yes!
This may help
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
John
SQL Server: Memory Manager : Total Server Memory?Hi
If you are only interested in the total used rather than say the amount
used by the cache or query optimiser then yes!
This may help
http://www.sql-server-performance.com/sg_sql_server_performance_article.asp
John
Best way to determine memory used?
What's the best perfmon counter for determining memory used by SQL? Is it
SQL Server: Memory Manager : Total Server Memory?
Hi
If you are only interested in the total used rather than say the amount
used by the cache or query optimiser then yes!
This may help
http://www.sql-server-performance.co...ce_article.asp
John
SQL Server: Memory Manager : Total Server Memory?
Hi
If you are only interested in the total used rather than say the amount
used by the cache or query optimiser then yes!
This may help
http://www.sql-server-performance.co...ce_article.asp
John
Best way to determine memory used?
What's the best perfmon counter for determining memory used by SQL? Is it
SQL Server: Memory Manager : Total Server Memory?Hi
If you are only interested in the total used rather than say the amount
used by the cache or query optimiser then yes!
This may help
http://www.sql-server-performance.c...nce_article.asp
John
SQL Server: Memory Manager : Total Server Memory?Hi
If you are only interested in the total used rather than say the amount
used by the cache or query optimiser then yes!
This may help
http://www.sql-server-performance.c...nce_article.asp
John
best way to copy database
I need to move a database from sql 2000 to sql 2005. My research suggests that I can backup a sql 2000 db from enterprise manager and restore it with sql 2005 management studio. Are there step by step directions for this or can someone tell me how to do this?
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. ThanksIMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS
As for syntax reference, try BOL. :)
HTH|||The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).
Any jobs/maintenance plans that will have to come over?|||I got it over with backup and restore and all the logins are there. No job or maintenance plans need to be restored. Thanks
what about logins? Are they transfered in this process. My research also suggests the I use copy database wizard by this failed on me and I can't find anything in the event log or anywhere else to tell me why. Please help. ThanksIMO, I would probably suggest backup and restore tactic for user databases. As for logins, either you dump the data into a file by doing a BCP out (bulk copy program) on master..syslogins table of the old server and BCP IN to the same table of the new server, however, since I've no experience on SQL2005, please check the structure of master..syslogins table if they're similar on both versions. As for the sysusers of user databases, you can make sp_adduser statements by doing a SELECT 'sp_adduser ' + NAME + ', ' + NAME + ' FROM MASTER..SYSLOGINS
As for syntax reference, try BOL. :)
HTH|||The BCP of syslogins trick quit working in SQL server 7.0. SQL 2005 has an all new system table structure, so I greatly doubt it would work here. For the logins, look up the CREATE LOGIN command. You will want to pay especial attention to the SID parameter. If you keep the SID for each user the same for SQL 2005 as it was on SQL 2000, they will match up with no problems. If you do not supply a SID for the CREATE USER command, a new random SID will be generated, and you will have to drop and recreate the user in the database (or recreate the login with the right SID).
Any jobs/maintenance plans that will have to come over?|||I got it over with backup and restore and all the logins are there. No job or maintenance plans need to be restored. Thanks
Subscribe to:
Posts (Atom)