Tag Info

Hot answers tagged

7

The following query will output the full contents of the current log in tabular format. If you only require a portion, replace the NULLs with LSNs. SELECT * FROM sys.fn_dblog(NULL,NULL) If th.e data is no longer in the log, then the following query will return the contents of your log backup. SELECT * FROM fn_dump_dblog (      NULL, NULL, 'DISK', 1, ...


5

You're unlikely to run into any issues with this but that said, it's rarely caution that gets a DBA fired. I'd probably go with installing a named instance, side-by-side with the existing instance. As you've got a test bed server up already I'd also suggest testing the application with 2012 Express. Why upgrade to an already out-dated version?


5

There is possibility of fragmentation issue with the index. Check possibility of index fragmentation - Here is a simple query to check fragmentation on your existing indexes: DECLARE @DBNAME VARCHAR(130); SET @DBNAME = 'MYDBNAME'; DECLARE @DBID INT; SET @DBID = DB_ID(@DBNAME); SELECT OBJECT_ID AS objectID , index_id AS indexID , ...


5

I have to presume that the application is written in such a way that it tries the insert, and if it fails, it just tries again. Otherwise the insert will fail (PK violation) and your users will have complained and filed bugs. If the purpose of this is to just assign unique numbers in a random order, then it may be the case that the application is working ...


2

Not sure how dbo is coming into play. Here is how you create a user in a database mapped to a server login, whether that login is already associated to a user in any other database: CREATE LOGIN foo WITH PASSWORD = 'bar', CHECK_POLICY = OFF; GO CREATE DATABASE splunge; GO USE splunge; GO CREATE USER foo FROM LOGIN [foo]; GO -- then permissions: GRANT ... TO ...


2

You can use the copy database wizard to move databases from one database instance to another. Right-click on an existing database, choose TASKS and then at very bottom, is 'Copy Database'. I would recommend the detach / re-attach database option, I don't like the other method, it's not reliable enough. You'll probably want to test a couple of database ...


2

Yes, in my opinion you should still use compression when backing up, even if the data is compressed. (Exceptions noted below.) The compression used during backup is able to work across all of the data, unlike page and row compression, which are constrained to only considering the data in each individual page, one page at a time. Think about a phone book in ...


1

My guess is they were trying to create a 4-byte GUID situation. Not sure what the benefit would be though: OK, it would avoid insertion 'hot spots' (at the expense of fragmentation built in the design). But being only 4 bytes the chances of collisions are vastly higher than a GUID. So it would fit a relatively small table with a very high rate of new ...


1

Indexes are optional on SQL Server tables. In fact, you can add constraints (primary key, unique) within the CREATE TABLE statement but you cannot add indexes at the same time. The indexes come later - this on its own should convince you that a table must be able to be created without indexes. This rules out option (4). In Clustered and Nonclustered ...


1

Based on the information you provided in the SQL script, the NT account which the SQL Server service is running under doesn't have access to the folder which the file is sitting in. You'll need to see which account the SQL Service is running under, then grant that account rights to the folder.


1

Index Fragmentation and out of date statistics! Don't forget stats, it's one of the most important factors in SQL Server deciding whether or not to use a index. Next time it runs slow, check out how fragmented the clustered and non clustered indexes are, and also check to see if you have AUTO_UPDATE Stats enabled!


1

It could be that your index is no longer being used by the SQL Server query plan generator. This can happen for a number of reasons. A big reason could be the row count in your table. Have you generated a query plan in SSMS to see if the index is being used. And, if it is, whether a seek or scan is being done? A rebuilding of the index may or may not help. ...


1

Install a workgroup instance. Install any service packs / cumulative updates so that its @@version is >= the express instance. Backup the database from Express, restore it on the new Workgroup instance, take the database offline in Express, and point your application(s) at the new instance name (you'll need to update their connection strings). I believe ...



Only top voted, non community-wiki answers of a minimum length are eligible