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

learn more… | top users | synonyms (2)

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
262 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
111 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
867 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
877 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 ...
7
votes
4answers
11k 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
892 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
352 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)
2
votes
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
474 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
1k 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
182 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
122 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
299 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
500 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 ...
4
votes
1answer
3k views

select permission was denied on the object 'sysjobs'

The select permission was denied on the object 'sysjobs', database 'msdb', schema 'dbo'. I have already tried to grant sqlagentoperatorrole and it didn't work. How do I resolve this error?
20
votes
3answers
39k views

How to change SQL Server collation

How could I change the SQL Server 2008 R2 Express Default Collation for the whole server and a particular database? Is there a way to do it using visual interface of SQL Server Management Studio? In ...
1
vote
2answers
5k views

Exporting data to excel

I want to execute some stored procedure and I want to export that result to Excel. How can Do that ? I'm new to SQL. any help or tutorials about this will appreciated.
1
vote
4answers
855 views

Update column of a large set of records with a calculated value

I am trying to figure out what a good way would be to write the pseudo query below in TSQL taking into account that there will be about 50 actualResult records and 100.000 prediction records per ...
1
vote
2answers
776 views

Specify a frequency range for a SQL agent schedule

I want to create a SQL agent schedule that occurs during a date range. I can't use "recurs every n weeks" because the process that it will fire off will change with each month. I need to set the ...
0
votes
1answer
380 views

SQL profiler not showing RPC calls

When I am running SQL profiler to trace the sql queries being executed, the trace is showing only Audit logs, not the sql commands being executed. Can somebody tell me how to get the sql commands ...
7
votes
3answers
2k views

Performance issue with xpath in SQL Server 2008

I have a table with lots of large xml-documents. When I run xpath expressions to select data from those documents I run into a peculiar performance issue. My query is SELECT p.n.value('.', 'int') ...
3
votes
2answers
1k views

How to select a subset of data from a large ER model?

I have a quite large ER model in my database and lots of data. Now I would like to take a subset of this data into a different database for testing purposes. The problem is how to get a subset from ...
1
vote
2answers
572 views

Can I send a string over TCP using T-SQL?

I've set up some alerts, based on this article Now I would like the Job that is triggered by the alert to send some information to a c# application (this application, can be running in the same ...
0
votes
1answer
1k views

Database design : multiple category of users login into same system

In our new system we have three type of people Business owner Assistant of business owner Staff (a skilled employee) I am having following entity [Business] - include detail about business ...
8
votes
3answers
16k views

How to prevent transaction log getting full during index reorganize?

We have multiple machines where we have pre allocated the size of the transaction log to 50gb. The size of the table that I am trying to reorganize is 55 - 60 gb but is going to continuously increase. ...
1
vote
3answers
1k views

sql server 2008 - controlling size of log file

I am getting this error. Msg 9002, Level 17, State 4, Line 2 The transaction log for database 'SalesDWH' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc ...
2
votes
2answers
160 views

Setting up a table with a calculated column

I'm setting up a table in sql server 2008. It going to hold three columns (busTime, trainTime, carTime). I want these columns to hold the amount of time a user spends traveling per day. I then want to ...
2
votes
3answers
1k views

Loading a FACT table per day

I have a database with 13 billion rows, per day I have around 20-30 mio rows. On top of this I have one cube, one of its dimensions is DateTime that goes down to milliseconds. To load the fact table I ...
0
votes
2answers
980 views

How to find out the activity which increasing the response time of the Sql Server?

We are using SQL Server 2008 as our database server. Recently we came to know, by using a 3rd party tool, that the response time of our database server is increasing at a specific time for example at ...
19
votes
2answers
4k views

Using EXCEPT in a recursive common table expression

Why does the following query return infinite rows? I would have expected the EXCEPT clause to terminate the recursion.. with cte as ( select * from ( values(1),(2),(3),(4),(5) ) ...
8
votes
2answers
2k views

how will nvarchar(max) store data in database will it be fast if some data is less then 4000 characters?

I have to develop a CMS which will support two Language English, Arabic. This CMS will be a sort of Article Publishing site. While designing & analysis i found that some articles are more than ...
2
votes
5answers
4k views

Keep transaction log size under control during batch processing

tl;dr I think my verbosity has obscured the real question I'm asking here, so I apologise for that. My main issue is that the checkpoint command appeared to be working for at least 800 iterations of ...
2
votes
1answer
15k views

In which folder / directory does SQL 2008 store files?

Where can I set the folder to store a database in when I create a new Database in the Microsoft SQL 2008 Server?
1
vote
1answer
67 views

How do I determine if a database restore file should be restored to the database based on backupset?

I have a production database that is fully backed up nightly and incrementally backed up hourly. On our backup machine, we have the differential backups and full backups restored ion a regular basis ...
3
votes
1answer
566 views

Sql Server Performance Degradation in moving from 2005 to 2008

I have sql server 2005 setup for my performance bench marking exercise and I have put same database on another server which has sql server 2008 and exact replica in terms of hardware configuration. ...
6
votes
1answer
3k views

why are people advised not to update through views

During College it was banged into our heads to not update tables through views, and again when I got into the workplace we where told do not update the database through views. Where there/Are there ...
5
votes
4answers
9k views

Optimising join on large table

I am trying to coax some more performance out of a query that is accessing a table with ~250-million records. From my reading of the actual (not estimated) execution plan, the first bottleneck is a ...
10
votes
1answer
895 views

SQL Server 2008 R2 Dirty reads - how non-atomic?

I am wondering "how dirty" dirty reads can get under a read-uncommited isolation level. I understand that rows that have been updated but not yet committed are visible, but: Can a row appear as ...
4
votes
1answer
1k views

Strange SQL Server 2008 behavior while using XLOCK and ROWLOCK

I wrote this script without COMMIT in a new Query window (Query1): BEGIN TRAN SELECT [RegionID], [RegionDescription] FROM [Northwind].[dbo].[Region] WITH(XLOCK,ROWLOCK) WHERE RegionID = 3 and in ...
1
vote
3answers
524 views

How can I know which tables “compose” a view?

I want to create a trigger on SYS.database_mirroring as it is not a real table, just a view... I can't do it. I want to know were the field mirroring_state_desc is coming from so that I can create a ...
1
vote
1answer
228 views

Create a trigger that compares a field, with a field in other table

I have 3 databases, (One principal, one Mirror and a witness) and I'm trying to make this system the most fault tolerant as possible. So I was thinking in doing some extra codding to protect me ...
0
votes
1answer
2k views

Deny INSERT/UPDATE/DELETE to all users for objects in a specific schema

I've got a payroll-related schema that I need to secure. I figure if I prevent all users from directly modifying the tables, and create a set of procedures to handle all data modifications, we should ...