Truncate Log didn''t work for all databases
-
Hi,
I'm a novice DBA and I'm trying to create stored procedure to truncate log for all databases (except for system databases) in my sql server.
Here are the scripts:
CREATE PROCEDURE USP_TruncateLog AS
SET NOCOUNT ON
DECLARE @dbname sysname
DECLARE @message varchar(255)
DECLARE @sqlstring varchar(255)
DECLARE @filename varchar(255)
DECLARE dblist_cursor CURSOR
FOR
SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN('master','msdb','model','tempdb')
OPEN dblist_cursor
FETCH NEXT FROM dblist_cursor INTO @dbname
WHILE @@fetch_status=0
BEGIN
SELECT @sqlstring = 'USE '+@dbname
EXECUTE (@sqlstring)
PRINT @@ERROR
PRINT @dbname
SELECT @filename = (SELECT name FROM sysfiles WHERE groupid=0)
PRINT @filename
SELECT @sqlstring = 'BACKUP LOG '+@dbname+' WITH TRUNCATE_ONLY'
EXECUTE (@sqlstring)
SELECT @sqlstring= 'DBCC SHRINKFILE('+@filename+')'
EXECUTE(@sqlstring)
FETCH NEXT FROM dblist_cursor INTO @dbname
END
DEALLOCATE dblist_cursor
SET NOCOUNT OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
When I execute the stored procedure somehow it truncates only on the current database (DBAdmin).
Here are the details messages:
0
APZODSDev
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
APZSTGDev
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
DBAdmin
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
PCAnalyzer
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
PCDomain
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
PCRepo
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
0
PMR
DBAdmin_Log
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Question:
Why the stored procedure only truncate the log in DBAdmin database, but NOT for all the databases. Any statement that I miss out?
Please reply my e-mail. I'm really appreciate it.
-
"I'm a novice DBA and I'm trying to create stored procedure to truncate log for all databases (except for system databases) in my sql server."
This is a very bad idea.
If the databases are in simple recovery mode, then the transaction log is automtically truncated on checkpoint, so you do not need this stored procedure.
If the databases are not in simple recovery mode, then the truncate log will not allow the database to be recovered until a full backup is made.
What is the real problem that you are attempting to solve?
Regarding the stored procedure:
Setting the current database with a "use database" only is valid for that specific EXECUTE and does not maintain the database context to subseqent EXECUTES, such as the "DBCC SHRINKFILE"
Here is a simplier solution based on:
The system databases always have dbids of 1 thru 4
DBID Name
1 master
2 tempdb
3 model
4 msdb
The transaction log file id is always 2
SQL
exec master.dbo.sp_execResultSet
@cmd =
'SELECT ''BACKUP LOG '' + DB.name + '' WITH TRUNCATE_ONLY'' + '' ''
+ ''use '' + DB.name + '' ''
+ ''DBCC SHRINKFILE(2)''
from master.dbo.sysdatabases as DB
WHERE DB.dbid > 4'
SQL = Scarcely Qualifies as a Language
-
Hi Carl,
I'm still working in SQL 2000 and cannot find sp_execResultSet. Anyway I've adopted your method to execute DBCC SHRINKFILE(2) and it works.
Really appreciate your prompt response.
Thanks a lot!
-
There are scripts here that will help if you really need this, but if you could answer some of Carl's first questions, we might be able to help you with other things.
-
sp_execresultset is part of SQL Server 2000 but is an undocumented system procedure, so you will not find it in Books OnLine.
If your server has a case sensitive collation, then the name must match exactly - "sp_execresultset" and "sp_ExecResultSet" are different.
However, truncating and shrink the transaction logs is a bad idea. Take the following senario:
1. At 1AM, the database is backed up
2. At 2AM , the transaction log is backed up.
3. At 3AM, the transaction is truncated using this process.
4. From 4AM until 2PM, there are hourly transaction log backups.
5. At 2:15 PM, there is a hardware failure and the database disks are gone. You will now need to tell the business that all updates after 2AM cannot be recovered.
If this is a development environment, then all database changes since 2AM, such as all stored procedure changes, are gone and all of the work will need to be redone.
6. at 2:30 PM, your employment is terminated.
What is the real problem ?
SQL = Scarcely Qualifies as a Language
-
without using shrinkfile
u can use shrinkdatabase that is a better option
like
BACKUP LOG DbName WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (DbName,0)
-
Please don't ever run ShrinkDatabase on a production database without a very, very good reason. All you're going to achieve is to fragment all your indexes badly and force the database to grow next time someone adds data, causing poor performance.
As Carl asked. Why are you trying to truncate the transaction log? Why are you trying to shrink the transaction log?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply