SQL detach (delete logs)

Question

Hi,

This is happening on SBS 2008

I'm new to sql first off all.

This is the problem:

Cpu 100%

looked for the problem and came out in sql.

Logs where to big.

I've logged in to sql with \\.\pipe\mssql$microsoft##ssee\sql\query

So i've detached WSS_SEARCH_HVBSRV_1 and deleted log files

then i tryed to reatach it ,here it went wrong . I got a fault saying it can't be attached.

So now months later memory i still going 96-98 % , because it can't find that database.

Events are 1827,1105,3760

Can somebody help me?

Regards,

Chris

PS: can you say excact what i have to do

Answer 1

Here are some other pics

Answer 2

Hi Chris,

If you have data file (.mdf) you can attach the database.

CREATE DATABASE WSS_Search_HVBSRV_1
ON (FILENAME = 'D:\WSS_Search_HVBSRV_1_data.mdf')
FOR ATTACH_REBUILD_LOG ;

Note:- here you need to replace the path of data filename with your path.

Ideally you should never delete the log file after detaching. There are other ways to reduce log file size. As on busy system doing this cause data loss.

---

- Chintak (My Blog)

Answer 3

Hi Chintak,

Thx for fast response.

I've also added NT AUTHORITY\NETWORK SERVICE to the database becuase there was a event saying failed login for this user

I've detached but now it raises events like

Insufficient SQL database permissions for user '' in database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query'. Additional error information from SQL Server is included below.

The EXECUTE permission was denied on the object 'proc_MSS_Cleanup', database 'WSS_Search_HVBSRV_1', schema 'dbo'.

and

Database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query' is not empty and does not match current database schema.

also have this

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Answer 4

Hi Chris_VL,

>> Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Regarding to the error message, seems might be related to the unplanned growth of the Data or Transaction log can take up space in the disk. Please check if the autogrow option for both Data and log are not set * By percent *. You can see this by selecting Database->properties->Data files tab and Transaction log tab.

Based on my research, "SQL Server only releases all the pages that a heap table uses when the following conditions are true: A deletion on this table occurs. A table-level lock is being held. Note A heap table is any table that is not associated with a clustered index.

If pages are not deallocated, other objects in the database cannot reuse the pages. However, when you enable a row versioning-based isolation level in a SQL Server 2005 database, pages cannot be released even if a table-level lock is being held.

For more information, please refer to Microsoft's solution from this KB. 

Additional please refer the following links for more information:
1. http://support.microsoft.com/default.aspx/kb/315512/EN-US/
2. http://support.microsoft.com/kb/272318/ : Shrinking the transaction log in SQL Server 2000 with DBCC SHRINKFILE
3. http://support.microsoft.com/kb/317375/ : A transaction log grows unexpectedly or becomes full on a computer that is running SQL Server
4. http://support.microsoft.com/kb/247751/:  BUG: Database maintenance plan does not shrink the database
5. http://support.microsoft.com/kb/305635/ :  A timeout occurs when a database is automatically expanding

---

Regards, Amber zhang

Answer 5

Thx for the replies

So i did manage to solve a few events with the permission rights and exceeding the limit of SBS 2008 4GB.

but can you help me with this one

Database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query' is not empty and does not match current database schema.

The server went from 98 to 87% memory usage ,so i see a bit off light in the tunnel.

Thx again for the responses

Christophe

Answer 6

Chris,

There are many different things going on here.

1. The log file for the database was deleted.

This could cause inconsistant data issues, among others. If the log file grows too large then investigate why and fix accordingly, we can help you with that.

2. Memory utilization is high.

I believe workgroup edition comes with SBS2008 but I'm not positive. If it is workgroup edition it'll use up to 4 GB of memory for the instance (not counting memory allocated outside of the BP). If this is too much, then Min and Max memory must be set appropriately.

3. Permissions, rights, etc.

It seems you have this one figured out?

-Sean

---

Sean Gallardy, MCC | Blog

Answer 7

Sean,

I've searched for the log file and it's still there,but my last error

Database 'WSS_Search_HVBSRV_1' on SQL Server instance 'np:\\.\pipe\MSSQL$Microsoft##SSEE\sql\query' is not empty and does not match current database schema.

How do i need to solve this if i don't have backup?

Permissions are ok i think. No more faults in event viewer.

For this faults

CREATE DATABASE or ALTER DATABASE failed because the resulting cumulative database size would exceed your licensed limit of 4096 MB per database.

Could not allocate space for object 'dbo.EventLog'.'PK_EventLog' in database 'SBSMonitoring' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

i've turned off SBS manager and SBSmonitoring service.

renamed SBSmonitoring.mdf and ldf  to sbsmonitoring-bak.mdf and sbsmonitoring_log-bak.ldf

and copied the new one there.

turned on services again.

Can you help with this?

Thx

Christophe