March 15, 2005 at 7:06 am
Hi there
I've recently acquired and hacked a backup and log script, basically this is to shrink the log file of all databases (It will be modfied later to just shrink user databases).
Currently the script is:
*********************************
DROP TABLE [$Temptable]
SELECT sysaltfiles.dbid AS DBID, sysaltfiles.name AS LOGFILE, sysdatabases.name AS DBNAME
INTO [$Temptable]
FROM sysdatabases INNER JOIN
sysaltfiles ON sysdatabases.dbid = sysaltfiles.dbid
WHERE (sysaltfiles.name LIKE N'%LOG %')
/*
Function: For each user database remove all the freespace and truncate the transaction log.
Instructions: Run against master, the script will use the system catalog to produce a list of databases
If you want to excluce a database add the excluded db name to the 'not in' list below.
Questions: cjm@integer.org
Updates: http://www.integer.org/cjm/files/remove-all-freespace-and-truncate-transaction-log.sql
Copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted provided this notice and the above comments are preserved.
*/
Set quoted_identifier off
use master
go
DECLARE @dataname varchar(30)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR select DBNAME from [$Temptable]
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
DECLARE @logname varchar(30)
DECLARE @logname_header varchar(75)
DECLARE lognames_cursor CURSOR FOR select LOGFILE from [$Temptable]
OPEN lognames_cursor
FETCH NEXT FROM lognames_cursor INTO @logname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM lognames_cursor INTO @logname
CONTINUE
END
SELECT @dataname_header = "" + RTRIM(UPPER(@dataname))
EXEC('use ' + @dataname)
FETCH NEXT FROM datanames_cursor INTO @dataname
SELECT @logname_header = "" + RTRIM(UPPER(@logname))
EXEC("DBCC SHRINKFILE (" + @logname+")")
FETCH NEXT FROM lognames_cursor INTO @logname
END
END
--PRINT " "--
--PRINT "Datafile: "+@dataname_header--
--PRINT " "--
--PRINT "Datafile: "+@dataname_header--
---exec ('use ' + @dataname)---
---exec ("DBCC SHRINKFILE (" + @logname+")")---
DEALLOCATE datanames_cursor
DEALLOCATE lognames_cursor
************************************************
But I get the following errors when I run it:
(9 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'templog' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'modellog' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'MSDBLog' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'pubs_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Server: Msg 8985, Level 16, State 1, Line 1
Could not locate file 'Northwind_log' in sysfiles.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
However if I run this script:
******************************************
DROP TABLE [$Temptable]
SELECT sysaltfiles.dbid AS DBID, sysaltfiles.name AS LOGFILE, sysdatabases.name AS DBNAME
INTO [$Temptable]
FROM sysdatabases INNER JOIN
sysaltfiles ON sysdatabases.dbid = sysaltfiles.dbid
WHERE (sysaltfiles.name LIKE N'%LOG %')
/*
Function: For each user database remove all the freespace and truncate the transaction log.
Instructions: Run against master, the script will use the system catalog to produce a list of databases
If you want to excluce a database add the excluded db name to the 'not in' list below.
Questions: cjm@integer.org
Updates: http://www.integer.org/cjm/files/remove-all-freespace-and-truncate-transaction-log.sql
Copyright (C) 2003 Cameron Michelis copying and redistribution of this file is permitted provided this notice and the above comments are preserved.
*/
Set quoted_identifier off
use master
go
DECLARE @dataname varchar(30)
DECLARE @dataname_header varchar(75)
DECLARE datanames_cursor CURSOR FOR select DBNAME from [$Temptable]
OPEN datanames_cursor
FETCH NEXT FROM datanames_cursor INTO @dataname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM datanames_cursor INTO @dataname
CONTINUE
END
DECLARE @logname varchar(30)
DECLARE @logname_header varchar(75)
DECLARE lognames_cursor CURSOR FOR select LOGFILE from [$Temptable]
OPEN lognames_cursor
FETCH NEXT FROM lognames_cursor INTO @logname
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status = -2)
BEGIN
FETCH NEXT FROM lognames_cursor INTO @logname
CONTINUE
END
SELECT @dataname_header = "" + RTRIM(UPPER(@dataname))
PRINT " "--
PRINT "Datafile: "+@dataname_header--
FETCH NEXT FROM datanames_cursor INTO @dataname
SELECT @logname_header = "" + RTRIM(UPPER(@logname))
PRINT " "--
PRINT "Datafile: "+@dataname_header--
FETCH NEXT FROM lognames_cursor INTO @logname
END
END
--PRINT " "--
--PRINT "Datafile: "+@dataname_header--
--PRINT " "--
--PRINT "Datafile: "+@dataname_header--
---exec ('use ' + @dataname)---
---exec ("DBCC SHRINKFILE (" + @logname+")")---
DEALLOCATE datanames_cursor
DEALLOCATE lognames_cursor
***************************************************
I get this output:
(9 row(s) affected)
Datafile: MASTER
Datafile: MASTER
Datafile: TEMPDB
Datafile: TEMPDB
Datafile: MODEL
Datafile: MODEL
Datafile: MSDB
Datafile: MSDB
Datafile: PUBS
Datafile: PUBS
Datafile: NORTHWIND
Datafile: NORTHWIND
Hence my confusion.
Thanks in advance.
March 16, 2005 at 6:16 am
Neither script will work, because DBCC SHRINKFILE only works in the current database. From TSQL, the only way to change the database context is with the USE statement (for example, USE Northwind)
The scripts contain this code, which you have commented out:
---exec ('use ' + @dataname)---
---exec ("DBCC SHRINKFILE (" + @logname+")")---
When exec ('use ' + @dataname) executes the database context is changed only for the duration of the EXEC. When control is returned to the next statement exec ("DBCC SHRINKFILE (" + @logname+")") the database context returns to the context of script. Assuming there are no other problems with the script, you will have to combine this into one call (I haven't fully analyzed it, but nested cursors...not good) :
EXEC ( 'use ' + @dataname + ' DBCC SHRINKFILE (''' + @logname + ''')' )
(NOTE: The above EXEC uses single quotes only. The script mixed single and double quotes).
--mike
February 28, 2006 at 8:59 am
I am using a different version of the same script. my problem is when I run it against a DB with periods in them such as S.A.A.A.A I receive a
DBCC execution completed. If DBCC printed error messages, contact your system administrator. [SQLSTATE 01000]
Database S.A.A.A.A [SQLSTATE 01000]
Msg 170, Sev 15: Line 1: Incorrect syntax near '.'. [SQLSTATE 42000]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply