This tag is for SQL Server 2008, major build version 10.00.xxxx.

learn more… | top users | synonyms (2)

3
votes
2answers
5k views

SQL Server 2008 T-SQL select hanging, but not dead locked

This is a pretty strange issue so bear with me. I have a stored proc that does some heavy duty processing. When it runs well it usually takes a few minutes depending on server load, but occasionally ...
0
votes
1answer
463 views

Coalesce Function Returning Duplicates

I have a many-to-many relationship setup between three tables. CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations I would like to retrieve all rows from the CompanyNotices table for ...
2
votes
1answer
332 views

Immediate e-mail alerting for error 229 (permission denied)

For a few reasons, I'd like to have all permission denied errors (number 229) emailed to me. Checking master.sys.messages shows that is_event_logged for this error is 0. Thus that rules out using SQL ...
5
votes
1answer
806 views

NHibernate Parameter Sniffing, SQL Server 2005 vs SQL Server 2008

NHibernate automatically uses sp_executesql with different paramters to execute it's data. Now what we're noticing in production is that we're beeing hit by parameter sniffing sometimes. In such a way ...
1
vote
2answers
3k views

SSMS: Show execution plan for a stored procedure as a single tree

I execute a stored procedure with an option "Include Actual Execution Plan". The execution plan always is represented by multiple statements each of which has its own execution plan. MSDN states the ...
8
votes
5answers
8k views

Pros/Cons Using multiple databases vs using single database

Working on a new project which has the requirement to use 7 databases. Arguing performance, stability, optimization is easier implemented. While I don't agree I'm having trouble collecting good ...
10
votes
5answers
4k views

Do empty columns take up space in a table?

I have table that holds from very basic info. Just a title and a few date fields. There's one field called comments which is varchar(4000) Most of the time we leave it blank, but some times will enter ...
3
votes
2answers
1k views

Trace unclosed connections

I get timeout errors every couple of days or so between IIS7 and an SQL 2008 Server. The error message I get is: System.InvalidOperationException: Timeout expired. The timeout period elapsed prior to ...
2
votes
1answer
193 views

What's a good indexing scheme for a type-2 SCD table with a very broad natural key?

I have a table which links several fairly long text fields (~100 chars) to a single text field: { A, B, C, D } => E. The dependent value can change, so I query data daily and record A, B, C, D, ...
1
vote
2answers
76 views

SQL Server 2008 error when changing cores

I have a virtual machine (virtualbox) with Windows Server 2003 and SQL Server 2008, running with 4 cores. I wanted to use 6, but when i changed the machine configuration, for some reason SQL Server ...
3
votes
1answer
379 views

Shrinking log file

Some of the sql log files (idf) are hugh like 5gb. Found a article (http://extended64.com/blogs/rhoffman/archive/2009/06/11/shrink-sql-server-2008-database-log-file-script.aspx) but not sure why he ...
0
votes
1answer
292 views

Shrinking log file [duplicate]

Possible Duplicate: Shrinking log file Some of the sql log files (idf) are hugh like 5gb. Found a article ...
0
votes
1answer
382 views

Returning Multiple Result-Sets or Single XML Blods

I've been writing some new Stored Procedures, and find myself returning between 6-8 result-sets in a few cases. I've always worked under the assumption that there were certain inefficiencies with ...
6
votes
4answers
1k views

What is the fastest way to reset SQL Server database if you can not use RESTORE?

I have integration tests that require test database to function. Since tests should generally be independent, I reset the database in the beginning of each test. I can not use RESTORE, since some ...
2
votes
1answer
413 views

How to retrieve / read SQL Image Data which is stored in the database?

From one application, we are storing all images in SQL database in the Image datatype. From another application, i need to retrieve the images and display it on User Interface. I am able to store the ...
1
vote
1answer
528 views

Implementing Pub/Sub initiated from the database

I have a number of state-full Windows Services that currently poll the database looking for new or changed records in specific Tables and/or Views. For obvious reasons this limits scalability and is ...
2
votes
1answer
454 views

Online Re-Indexing In SQL Server 2005/2008 Standard

The Story: Once a week, we run a special Maintenance Plan that involves re-indexing 2 critical tables that have ~6 Million records between the two of them. We use SQL Server 2005 Standard Edition. ...
1
vote
2answers
364 views

Is it good practice to assign a system account for database owner?

I can see the owners of my databases usign this query SELECT name , SUSER_SNAME(owner_sid) FROM sys.databases What are the things I should consider when choosing an account for database ...
3
votes
1answer
3k views

Storing XML in varchar(MAX) or use SQL-XML

I am defining a schema for a new set of resources using SQL Server 2008... In this case, each record (e.g. row) will need to store XML fragments. From time-to-time; although not frequently; I'll need ...
1
vote
1answer
70 views

tsql to know when a database was last shrunk

In SQL 2000, 2005 and 2008, how can I know when databases were last shrunk on a MS SQL server? I want to do this using TSQL. Regards Manjot
3
votes
1answer
3k views

To 'DBCC SHRINKDATABASE' or Not To 'DBCC SHRINKDATABASE': That's the question

We're freeing lots of space in a SQL Server 2008 R2 database -a Loooot! enough to care about- (we're dropping lots of unnecessary data). but the database file conserves its file size. we want to ...
1
vote
3answers
843 views

SQL Server tempdb question

From what I understand the system database is used to hold temporary tables, intermediate results and other temporary information. On one of my database instances I have a tempdb that is seems very ...
1
vote
1answer
623 views

IS_SRVROLEMEMBER returning NULL for valid non windows user

I have a service that executes a stored procedure to query the db for results. The stored procedure needs to use impersonation on the user who is logged in the client (since the service has its own ...
2
votes
2answers
5k views

SQL Server 2008 DBCC Problems

We have a database Ms DBCC CHECKDB (MS) Or ALTER DATABASE MS SET SINGLE_USER DBCC CHECKDB(MS,REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE MS SET MULTI_USER Error message Msg 0, Level 11, State 0, ...
1
vote
1answer
266 views

DB Integrity Check Lock Database

Hi all i'm using The Integrity Check script from http://ola.hallengren.com to check through my database of roughly 65 million records. I was just wondering if this will lock my database? In terms of ...
0
votes
1answer
849 views

SQL Server 2008 - Restore Database With RESTART

I am trying to restore a database from a backup file on a SQL Server 2008 instance using the following SQL command: BEGIN RESTORE DATABASE [MyDataBase] FROM DISK=@db_location WITH ...
2
votes
3answers
2k views

Undo Database Restore in SQL Server 2008

I accidentally restored a SQL Server 2008 database with a wrong backup. Is there any way to undo this and restore to previous version?
1
vote
1answer
575 views

Exclude .LOG file while restoring a database

I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but ...
0
votes
1answer
2k views

detaching database results in error “SQL Server detected a logical consistency-based I/O error”

I'm trying to detach a database and I'm getting this error : SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:234; actual 0:0). It occurred during a read ...
0
votes
1answer
12k views

How to backup and restore a database to a different location and name via command line only

I have a database that I want to copy from one location to another (maybe even to a different computer) via the command line only. The name of the database on the new location should not be identical ...
1
vote
1answer
256 views

Table Partitioning. What is the correct process for deleting .ndf and .ldf files?

Please go easy it’s a steep learn curve.. OK steps done so far Create files, Create file groups, Create partition function, Create partition scheme, Partition table associate files with filegroups ...
0
votes
1answer
109 views

SQL Server Point in Time Restore Scenario

Hopefully you can help out on this as I may be up a creek! I am working with a database using the FULL recovery model. The database has had nightly database backups but their transaction log has ...
1
vote
1answer
2k views

Recreate MSDB for SQL Server Express 2008

My SQL Server Express database's msdb file has become 'suspect'. All the other mdf files seem to be OK. I now need to recreate the msdb file, but I do not have a backup for it (guilty as charged!). ...
1
vote
1answer
838 views

create table script with constraints in sql server 2008

I have create table scripts and alter table scripts with some constraints for that database. It take time as twice to create the table and alter the table .. Is this possible to combine create ...
8
votes
1answer
821 views

How to get accurate query performance?

I'm attempting to improve performance of a stored procedure. When I run the SP, it finishes almost instantly, as if something were cached. I was told to use the following two lines of SQL before ...
6
votes
4answers
10k views

When to add indexes to #temp tables?

When testing stored procedures in SSMS, it sometimes says there is a missing index on #someTempTable____________________000000000000005B] (someField) etc etc When I do add them like this to the sp: ...
6
votes
3answers
871 views

Why is @temp table performance some times worse than #temp table performance?

I was recently working in a very slow stored procedure (took 5 minutes to run). I made a very small tweak from doing this: declare @tempTable table ( ... ) insert into @tempTable select ..... to ...
2
votes
1answer
314 views

Sql Server 2005 Copy Only backup

I have a database in Sql Server 2005. The database is configured to be backed up as Full (sunday) and Differential (weeknights) on a mediaset at a particular path. When I run the command: BACKUP ...
6
votes
3answers
5k views

LIKE to select the independent existence of the word whereever in the text

I have a table with the following texts and the key word i am searching for is 'Search'.So i have written a query SELECT [ID] ,[TextValue] FROM [dbo].[SearchLike] WHERE [TextValue] LIKE ...
1
vote
3answers
2k views

How to know if Instant File Initialization is enabled?

Besides creating a new database and timing it, is there a simple way to determine whether or not Instant File Initialization is enabled/functioning? (on Windows Server 2008 Enterprise)
1
vote
2answers
8k views

Why does restore take a long time?

Although, the database backup file is only 2GB, the restore can take up to 15-20 minutes even on a x64 12G RAM, i7 machine. When I backup similar sized backup files made from a different database, the ...
5
votes
2answers
471 views

SQL Server index reorg

While index rebuild will require at least same amount of space in the data file as current index index (as a copy of the index will be created) My reading on SQL Server BOL related to reorg it only ...
0
votes
1answer
966 views

Sql Server merge replication primary key violations

Using Sql Server 2008. Pretend I have a publisher and 2 subscribers A and B. The replicated table has a guid on it and also a primary key that is not the guid. Frequently, updates happen at both ...
4
votes
2answers
4k views

How to handle deadlock issue sql server 2008?

I have a simple application.This application update data of a table (B) from another table(A) which is changed every 60 sec. User may read data at any time so creating deadlock. How is this sort of ...
1
vote
1answer
180 views

Sql Server Agent REPL Jobs

I know that replication uses Sql Agent jobs to pull subscriptions. Are these jobs supposed to stay in an 'executing' state? (Sql 2008 SP1)
1
vote
1answer
121 views

Log all user error messages for retrospective troubleshooting

Suppose I want to log all severity 16 (and higher) user error messages to a database table in order to allow some retrospective application troubleshooting (since the answer to "Can you send me the ...
1
vote
1answer
2k views

Can I check if a Mirror database was synchronized when the principal died?

having 2 machines, one being the principal and the second one as a mirror in case of failure of the 1st one. without witness but in High Safety mode Is any way to check if the mirror DB was ...
-1
votes
1answer
296 views

Does SQL Server full restore set the differential base?

Differential backups are taken as the difference between the current state, and the last full backup called the "differential base". Does restoring a full backup reset the differential base? If so, ...
2
votes
2answers
488 views

Alternatives to sp_indexoption for Row Locks and Page Locks in SQL Server 2008

Are there better alternatives to setting the Row Locks and Page Locks in SQL Server 2008? The case here is to set the row and page locks to false on the table. The lock is in effect during a series ...
7
votes
1answer
8k views

Auto Update Statistics in SQL Server 2008R2: Why do some statistics remain stale despite a large number of row inserts?

During investigation of a slow query it appeared that the execution plan was exceptionally suboptimal (A nested loop performing 9 million executions of a seek where the estimated number of executions ...