It’s true that you don’t want to continually shrink and grow the log file for a few different reasons including fragmentation and incurring the overhead of growing files during a request. However, if you have decided that your transaction log is in fact too large and needs to be shrunk, here is one way to go about it. [Read more…] about Shrinking and Sizing Transaction Logs
SSIS Foreach Loop Container and Access Databases
I was struggling today to get a Foreach Loop Container to trawl a directory full of Access databases. The problem was that I had not correctly built the ConnectionString for my source connection using the Expression Editor. I found my answer at http://www.mssqltips.com/tip.asp?tip=1437. I’ll give you a preview: “Data Source=” + @[User::mAccessLocation] + “;Provider=Microsoft.Jet.OLEDB.4.0;User ID=;Password=;”
Log File Size For DB Restores
Keep the size of the transaction log file in mind when you are restoring a database.
Over the weekend, the backup drive on one of our servers ran out of space. Without transaction log backups, the log grew several times until it consumed the log disk. At that point SQL choked.
We straightened out the SQL problems and things ran smoothly the rest of the weekend. Today we went to restore the backup to a test server. At this point we ran into a problem I haven’t seen before but should’ve anticipated–the backup wouldn’t restore. [Read more…] about Log File Size For DB Restores
Setup for SQL Server 2005 Developer Edition Fails With MSXML6 Errors
I was trying to install SQL Server 2005 Developer Edition on an XP SP3 workstation today but setup kept failing. The setup screen looked like this:
[Read more…] about Setup for SQL Server 2005 Developer Edition Fails With MSXML6 Errors
Get Space Used By Files in All Databases
DECLARE @dbs TABLE (dbname NVARCHAR(200))
DECLARE @wrkStatement NVARCHAR(200)
DECLARE @wrkSql NVARCHAR(4000)
INSERT INTO @dbs(dbname)
SELECT name
FROM master..sysdatabases
WHERE name NOT IN ('model', 'master', 'msdb')
ORDER BY name
CREATE TABLE #tmp123_results (
[Db] NVARCHAR(100),
[FileId] INT,
[LogicalName] NVARCHAR(655),
[AllocatedMB] DECIMAL(12, 2),
[UsedMB] DECIMAL(12, 2),
[FreeMB] DECIMAL(12, 2),
[PercentUsed] DECIMAL(12, 2),
[PercentFree] DECIMAL(12, 2),
[PhysicalName] NVARCHAR(655)
)
DECLARE @curr_db NVARCHAR(100)
DECLARE @crsSelect CURSOR
SET @crsSelect = CURSOR FOR
SELECT dbname
FROM @dbs
OPEN @crsSelect
FETCH NEXT FROM @crsSelect INTO @curr_db
WHILE @@FETCH_STATUS = 0
BEGIN
SET @wrkStatement = '['+@curr_db+ '].dbo.sp_executesql'
SET @wrkSql = 'INSERT INTO #tmp123_results(Db,FileId,LogicalName,AllocatedMB,UsedMB,FreeMB,PercentUsed,PercentFree,PhysicalName)
SELECT
DB = '''+@curr_db+''',
a.FILEID,
NAME = a.NAME,
ALLOCATED_MB = convert(decimal(12,2),round(a.size/128.000,2)),
USED_MB = convert(decimal(12,2),round(fileproperty(a.name,''SpaceUsed'')/128.000,2)),
FREE_MB = convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) ,
PercentUsed = 100 * (convert(decimal(12,2),round((fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
PercentFree = 100 * (convert(decimal(12,2),round((a.size-fileproperty(a.name,''SpaceUsed''))/128.000,2)) / convert(decimal(12,2),round(a.size/128.000,2)) ) ,
FILENAME = a.FILENAME
FROM [' + @curr_db + '].dbo.sysfiles a'
EXEC @wrkStatement @wrkSql
FETCH NEXT FROM @crsSelect INTO @curr_db
END
CLOSE @crsSelect
DEALLOCATE @crsSelect
SELECT * FROM #tmp123_results ORDER BY Db, FileId
DROP TABLE #tmp123_results
Shrinking Databases and Files
Shrinking should not be done on a regular basis. [Read more…] about Shrinking Databases and Files