Tables to clear when space is an issue

delete from dbo.LMK_POP10100_PURCHASEORDER_HDR where docdate < '2010-01-10'
delete from dbo.LMK_POP10110_PURCHASEORDER_DET where reqdate <= '2010-01-01'
delete from dbo.LMK_AR_Errors_Data_To_GP
delete from dbo.LMK_BSB10100_OUTBOUND_Errors
delete from dbo.LMK_Mat_Issues_Errors_Data_To_GP
delete from dbo.LMK_Payables_Credit_Errors_Data_To_GP
delete from dbo.LMK_Payables_Errors_Data_To_GP
delete from dbo.LMS_GP_MFG_CustomerMaster_error
delete from dbo.BV_AR_Errors_Data_To_GP
delete from dbo.BV_Payables_Credit_Errors_Data_To_GP
delete from dbo.BV_Payables_Errors_Data_To_GP

SQL 2005 PAGE FILE BIG

Today we had a page file size issue, due to this the memory utilization was 16 GB, this stop all the integrations and giving out time out errors, the following actions where taken to resolve this.

1. Restart the sql server.
2. Check the database size.
3. Shrink the database mdb and log file size using the following scripts.
4. Check the max table size.

use manufacturing
exec sp_helpfile
dbcc shrinkfile (Manufacturing_Data)

SQL Database on Suspect Mode

We had a scenario in our US office where the Sql server was abruptly stopped due to power outage.

This has caused the database to go on SUSPECT Mode.

To get the database back on line the following sql scripts can be used.

The below script is used to check the database.

DBCC CHECKDB ('ManufacturingTulsa') WITH NO_INFOMSGS,

ALL_ERRORMSGS

The below script is used bring the database on line.

EXEC sp_resetstatus 'ManufacturingTulsa';

ALTER DATABASE ManufacturingTulsa SET EMERGENCY

DBCC checkdb('ManufacturingTulsa')

ALTER DATABASE ManufacturingTulsa SET SINGLE_USER WITH ROLLBACK IMMEDIATE

DBCC CheckDB ('ManufacturingTulsa', REPAIR_ALLOW_DATA_LOSS)

ALTER DATABASE ManufacturingTulsa SET MULTI_USER