Tag Info

Hot answers tagged

4

It is very much normal for there to be gaps in identity values (caused by rolled-back transactions, over estimated pre-allocations, and so forth) as you are already aware, though that sort of sparse use of values is rather unusual and indicates that there is something going on which you need to be aware of (either a design issue that may be unavoidable and ...


3

In SSMS, right-click on the database and go to "Reports", "Standard Reports", "Disk Usage by Table". It will tell you the total size, the data size, the index size, and the unused size for each table (as well as the row count).


3

It's been answered on Stack Overflow: SELECT t.NAME AS TableName, s.Name AS SchemaName, p.rows AS RowCounts, SUM(a.total_pages) * 8 AS TotalSpaceKB, SUM(a.used_pages) * 8 AS UsedSpaceKB, (SUM(a.total_pages) - SUM(a.used_pages)) * 8 AS UnusedSpaceKB FROM sys.tables t INNER JOIN sys.indexes i ON t.OBJECT_ID = ...


3

This is what I use to monitor sleeping SPIDs causing blocking issues: SELECT ,s.session_id ,s.status ,s.login_time ,s.host_name ,s.program_name ,s.host_process_id ,s.original_login_name ,s.last_request_end_time ,CAST(t.text AS nvarchar(4000)) AS [text] FROM sys.dm_exec_sessions AS s INNER JOIN sys.dm_exec_connections AS ...


3

Are there exceptions to the above clustered index rules? The general guidance for selecting a clustered index is good, but there are sometimes additional considerations to be taken into account. Occasionally, these extra factors can be more important than the general 'rules'. Your scenario is somewhat "special" in that you have a very wide table, with a ...


2

Is there a way to get updates automatically? SQL Server 2008 R2 is not supported on Windows 10. This means the SQL Server team will not be releasing any security updates or patches if they find any specific issues related to SQL Server 2008 R2 code. There is no way to get updates automatically as none would be released related to Windows 10. ...


2

DTSInstaller.exe consumes a file called with an extension of SSISDeploymentManifest The contents of it look like <?xml version="1.0"?> <DTSDeploymentManifest GeneratedBy="HOME\administrator" GeneratedFromProjectName="SSISHackAndSlash" GeneratedDate="2010-04-30T21:19:08.6336677-05:00" AllowConfigurationChanges="true"> ...


2

You can clear the log during the REORG process as its not done as a single transaction. The log bloating depends on index fragmentation level and amount of work done. Again, REORG is a fully logged operation on all recovery models. https://technet.microsoft.com/en-us/library/ms191484(v=sql.105).aspx


1

The easiest way is to look into the transaction log and see where there is a sequence that you can 'split'. Start by reading How to read and interpret the SQL Server log to understand what I'm talking about. Then look into your DB log for a sequence like this: lsn1 BEGIN XACT xid1 lsn2 some update lsn3 COMMIT xid1 lsn4 BEGIN XACT xid2 lsn5 some ...


1

Refer to the link Aaron Bertrand provided. There are a limited set of operations that are minimally logged, but reorg/rebuild aka defragmenting are not included. If you wish to minimize the effect, you'd have to do incremental reorgs as mentioned by Cibi or just allocate the needed space if this is expected to occur regularly. I've had to do this before in ...


1

It is in fact a configuration issue in the Oracle Client Set NLS_LANG to AMERICAN_AMERICA.WE8MSWIN1252 and no more conversion error. UPDATE Why? Because 0.6 in French would be read as a character value whereas in English it remains numeric. 0,6 in French is 0.6 in English. Defining NLS_LANG as American means that 0.6 stays numeric thus allowing the ...


1

Just a quick thought on something to look into, in a domain type environment, some operating systems allow you to logon to the server before full network connectivity is established. You may want to check for either local or domain level group policy settings to not allow logon or OS startup until full network connectivity is established. Just in case ...


1

Converting my comment as answer (SQLFiddle): ;with cte (cityID, label, rowNum, stateAbbrev, stateName) as ( SELECT TOP 100 City.id AS cityID, City.name + ', ' + [State].[State] as label, row_number() over (partition by cast(City.name + ', ' + [State].[State] as varchar(max)) order by City.id desc) AS rowNum, -- if you want the highest ...


1

Another approach that seems not to have been mentioned yet is to have Hazards and Tasks use the same ID space. If a Hazard has a Task, it will have the same ID. If a Task is for a Hazard it will have the same ID. You would use a sequence rather than identity columns to populate these IDs. Queries on this type of data model would use (full) outer joins to ...


1

The query linked to, and copied by, @Nelson is inaccurate: it ignores Indexed Views, Full Text Indexes, XML Indexes, etc. If you want a query that will include everything without executing sp_spaceused via sp_MSForEachTable, then I have already posted two variations of it (one here on DBA.StackExchange and the other on StackOverflow) so I won't copy them ...



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