SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2008

To shrink the transactional logs in SQL Server 2005 follow this link:
https://robbamforth.wordpress.com/2009/11/16/sql-how-to-shrink-un-shrinkable-database-log-database-log-wont-shrink/

In SQL Server 2008 it’s a matter of changing the recovery model to SIMPLE and re-running the DBCC SHRINKFILE  command – assuming the current recovery model is FULL.

Firstly, here is a quick script to backup the database and also the transactional log (if required to back up separately) to disk:

— QUICK SCRIPT TO BACKUP THE DATABASE TO DISK
BACKUP DATABASE [DATABASE]
TO DISK = ‘c:\backup\DATABASE.BAK’
WITH NOFORMAT, NOINIT, NAME = ‘DATABASE-Full Database Backup’,
SKIP,NOREWIND,NOUNLOAD,STATS = 10— QUICK SCRIPT TO BACKUP THE LOG FILE TO DISK
BACKUP LOG [DATABASE]
TO DISK = ‘c:\backup\DATABASE_LOG.BAK’
WITH NOFORMAT, NOINIT,  NAME = ‘DATABASE-Transaction Log  Backup’,
SKIP, NOREWIND, NOUNLOAD, STATS = 10

 

 

Step 1: View the current recovery model configuration.

— SHOW AVAILABLE DATABASES AND THEIR CURRENT RECOVERY MODEL CONFIGURATION
select name,recovery_model_desc from sys.databases

 

Step 2: Change the recovery model to SIMPLE.

–CHANGE THE RECOVERY MODEL CONFIGURATION TO SIMPLE
Alter database [DATABASE] set Recovery simple

 

Step 3: Use DBCC SHRINKFILE to truncate the transactional logs of the database.

— USE BDCC CHRINK FILE TO TRUNCATE THE TRANSACTIONAL LOGS
USE [DATABASE]
GO
DBCC SHRINKFILE ([DATABASE_LOG], 0, TRUNCATEONLY)
GO

 

Step 4: Return the recovery model back to FULL if required.

— CHANGE TEH RECPVERY MODEL BACK TO FULL IF REQUIRED
Alter database [DATABASE] set Recovery full

 

  

Advertisements

SQL – How To Shrink Un-shrinkable Database Log / Database Log Won’t Shrink SQL Server 2005

To shrink the transactional logs in SQL Server 2008 follow this link: https://robbamforth.wordpress.com/2010/06/24/sql-%e2%80%93-how-to-shrink-un-shrinkable-database-log-database-log-won%e2%80%99t-shrink-sql-server-2008/

The following should only be considered as a last resort to force the log file to shrink. I suggest you take a full database back-up first.

The DBCC LOGINFO can be run against the database. This will give you information about your virtual logs inside your transaction log.
In particular look at the Status column. Status = 2 indicates the portions of the log that are in use, status = 0 are not in use.

DBCC LOGINFO (DATABASE_NAME)

Step 1:
Attemp to shrink the problematic log file and back up the database with TRUNCATE_ONLY.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

Step 2:
Create a temporary table and insert a record.

CREATE TABLE MyTable (MyField VARCHAR(10), PK INT )
INSERT Mytable (PK) VALUES (1)
GO

Step 3:
Run the following loop to update the table.

SET NOCOUNT ON
DECLARE @Index INT
SELECT @Index = 0
WHILE (@Index < 20000)
BEGIN
   UPDATE MyTable SET MyField = MyField WHERE PK = 1
   SELECT @Index = @Index + 1
END
SET NOCOUNT OFF

The log rows with status 2 should now be available with statis = 0.

Step 4:
Re-run the shrink command.

USE [DATABASE_NAME]
GO
DBCC SHRINKFILE (N’DATABASE_NAME_LOG’ , 0, TRUNCATEONLY)
GO

BACKUP LOG [DATABASE_NAME]
WITH TRUNCATE_ONLY

— Drop the temporary table as no longer required
drop table MyTable

The log file should now have shrunk successfully.

Source: http://www.broad-lea.com/sql_server/sql_reduce_log_size.html

Select column names and column data from SQL table

In SQL Management Studio Select the database environment that contains the table that you want to query.
The following command displays all the columns in all the table in thje current database:

select * from information_schema.columns

 You will notice that this result set returns usefull data on the table.

To select this information from a specific table in the  database,
use the following command:

select * from information_schema.columns

where TABLE_NAME = ‘YOURTABLE’

  where YOURTABLE is the name of the table you wish to select on.

SQL 2005 SERVER SET UP

I have set up a new server this week to host SQL server 2005 to manage my count engine.

Spec:

 – Antec Sonata 3 Super Case with 500W aPFC PSU
 – Abit AB9 WiFi iP965, S775, PCI-E (x16),
    DDR2 533/667/800, SATA II, SATA RAID, ATX
 – 4GB (2x2GB) Corsair TwinX XMS2, DDR2 PC2-6400 (800),
    240 Pins, Non-ECC Unbuffered, CAS 5-5-5-18
 – Intel Core 2 Quad Q6700, Kentsfield Core, Socket 775,
    2.66 GHz 1066MHz 8MB Cache, Retail
 – Pioneer DVR-215DBK 20x DVD±R, 10x DVD±DL,
    DVD + RW x8/-RW x6, SATA, Black, OEM
 – 1000 GB Hitachi HDS721010KLA330 Deskstar 7K1000,
    SATA II, 7200 rpm, 32MB Cache, 8.5 ms, NCQ
 – 500 GB Hitachi 0A33437 Deskstar 7K500, SATA II,
    7200 rpm, 16MB Cache, 8.5 ms, NCQ
 – Edimax 9270TX 64Bit 10/1000 Gigabit (GbE) PCI
    Network Card

I’ve done a lot of research into getting the most out of my hardware. After some time I concluded the following set up to be the best;

Windows Server 2003 OS
SQL Server 2005 32Bit.

To get the most out of the RAM using the 32Bit installation of SQL Server 2005 I needed to to tweak the boot sequence using the /3GB switch – to allow 3 og the 4 GIG of RAM available for processing.

Start –> Right-click My Computer –> Properies.
Advanced Tab –> Startup and Recovery –> Settings –> Click the Edit button to edit BOOT.ini

Boot.ini;

[boot loader]
timeout=30
default=multi(0)disk(0)rdisk(0)partition(1)\WINDOWS
[operating systems]
multi(0)disk(0)rdisk(0)partition(1)\WINDOWS=”Windows Server 2003, Enterprise” /noexecute=optout /fastdetect /3GB

the /3GB switch applies to all 32-bit applications running on the box (not just SQL Server).
It reduce the kernel mode memory to just 1GB, thereby allowing the user mode portion 3 of the 4GB in the virtual address space.

The next key setup process was to set up the HDD to spread the work load. The following is how Microsoft suggest setting up HDD in SQL server;

C:  (RAID1 mirror for redundancy)
O/S
SQL Installation

D:  (RAID1 mirror for improved read and redundancy, or RAID10 for improved read/write and redundancy)
MDF Files

E: (RAID1 for redundancy if you are not backing up database or log files to a NAS, otherwise just a standard drive is ok)
Log Files
 
F: (RAID0  for fast read/write, no redundancy required as it’s just temp working, or a standard drive)
TempDB

I only had 2 HDD in my server so I did the following;

c:
Winders Server 2003
SQL Server 2005

F:
Data Files (MDF)
LOG Files
TEMPDB

which works fine for my purposes. If I need to increase processing speed I’ll add further HDD in the future.