Category Archives: SQL Server 2008 R2

Saving changes is not permitted while modifying table design

1 Reply

Problem: There may be scenario while modifying the table design which needs to re-create the table. Even with the full permission while modifying it from SSMS, you get the below message and your table changes will be rolled back.

Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.

Solution:
Below are the few scenarios where changes in table design will re-create the table.

Changing the column data type
Changing the NULL property
Changing the order of the columns
Adding column before the last column
Changing the identity column property
Changing the computed column expression

Let’s see how can we fix the “Saving Changes is not permitted …”

Go to SSMS – Select Tools menu -> click on Options -> Go to Designers tab page -> Click on “table and database designers” page -> Un-check “Prevent Saving changes that require table re-creation” check box.

Powershell Script to get total and free disk space

Leave a reply

You can execute the below powershell script to get total & free disk space which includes the mount drive as well.

Get-WmiObject -class Win32_volume -filter "drivetype = 3" | select name,
 @{Name="Capacity";Expression={$_.capacity / 1GB}},@{Name="freespace";Expression
={$_.freespace / 1GB}}

SQL Server Management Studio Error 916

3 Replies

Problem
When connecting to SQL Server using Management Studio (SSMS), with the limited permissions, you do not see any user databases or receive Error 916 when expanding the database list from Object Explorer. The error message is “The server principal “Login Name” is not able to access the database “database name” under the current security context. (Microsoft SQL Server, Error: 916).” In this tip I will explain the root cause of the issue and how to fix it.

Solution
http://www.mssqltips.com/sqlservertip/2761/sql-server-management-studio-error-916/

DBCC Config

Leave a reply

Dbcc Config is an undocumented DBCC command which returns SQL server configuration. Configuration details are stored in Master database file 1 and on page 10.You have to enable the trace flag 3604 to get the result.

dbcc traceon(3604)
dbcc config
dbcc page(master,1,10, 3) 

CONFIG:


DS_CONFIG @0x032D6060

cconfsz = 8                          cmajor = 8                           cminor = 0
crevision = 10                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 8                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgIOAffinityMask = 0                cfgbuf = 4362
cfgdes = 0                           cfglocks = 0                         cfgquerytime = 600
cfgcursorthrsh = -1                  cfgrmttimeout = 10                   cfg_dtc_rpcs = 0
cclkrate = 31250                     cfg_max_text_repl_size = 65536       cfgupddate = 40947
cfgupdtime = 22332689                fRemoteAccess = 1                    cfgbufminfree = 331
cnestedtriggers = 0x1                cdeflang = 0                         cfgTransformNoiseWords = 0x0
cfgPrecomputeRank = 0x0              crossdbownchain = 0                  cidCfg = 0x3400d008
cidCfgOld = 0x3400d008               cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033
maxworkthreads = 0                   minworkthreads = 32                  minnetworkthreads = 32
threadtimeout = 15                   connectsperthread = 0                cusroptions = 0
exchcostthreshold = 5                maxdop = 0                           cpwdpolicyupgrade = 0x1
cfServerTriggerRecursion = 1         cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60
CLREnabled = 0                       cfgmaxcrawlrange = 4                 ftSmallBufMin = 0
ftSmallBufMax = 100                  ftLargeBufMin = 0                    ftLargeBufMax = 100
RemoteDacEnabled = 0                 CommCritComplianceEnabled = 0        EkmEnabled = 0
cUserInstanceTimeout = 0x3c          cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0
FilestreamAccessLevel = 0            OptimizeForAdhocWorkloads = 0        cchecksum = 1787
DBCC execution completed. If DBCC printed error messages, contact your system administrator.



PAGE: (1:10)


BUFFER:


BUF @0x043659F8

bpage = 0x069A4000                   bhash = 0x00000000                   bpageno = (1:10)
bdbid = 1                            breferences = 0                      bUse1 = 45452
bstat = 0x2c00009                    blog = 0x43212159                    bnext = 0x00000000

PAGE HEADER:


Page @0x069A4000

m_pageId = (1:10)                    m_headerVersion = 1                  m_type = 14
m_typeFlagBits = 0x0                 m_level = 0                          m_flagBits = 0x200
m_objId (AllocUnitId.idObj) = 99     m_indexId (AllocUnitId.idInd) = 0    Metadata: AllocUnitId = 6488064
Metadata: PartitionId = 0            Metadata: IndexId = 0                Metadata: ObjectId = 99
m_prevPage = (0:0)                   m_nextPage = (0:0)                   pminlen = 0
m_slotCnt = 1                        m_freeCnt = 0                        m_freeData = 8190
m_reservedCnt = 0                    m_lsn = (0:0:1)                      m_xactReserved = 0
m_xdesId = (0:0)                     m_ghostRecCnt = 0                    m_tornBits = -1715479820

Allocation Status

GAM (1:2) = ALLOCATED                SGAM (1:3) = NOT ALLOCATED           
PFS (1:1) = 0x64 MIXED_EXT ALLOCATED 100_PCT_FULL                         DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED            

Slot 0, Offset 0x60, Length 9, DumpStyle BYTE

Record Type = PRIMARY_RECORD         Record Attributes =                  Record Size = 9

Memory Dump @0x6300A060

00000000:   00000800 fb060000 08†††††††††††††††††....û....                

DS_CONFIG @0x6300A060

cconfsz = 8                          cmajor = 8                           cminor = 0
crevision = 10                       cbootsource = 2                      crecinterval = 0
ccatalogupdates = 0                  cmbSrvMemMin = 0                     cmbSrvMemMax = 2147483647
cusrconnections = 0                  cnetworkpcksize = 4096               ckbIndexMem = 0
cfillfactor = 0                      ctapreten = 0                        cwritestatus = 0
cfgpriorityboost = 0x0               cfgexposeadvparm = 0x1               cfglogintime = 20
cfgpss = 0                           cfgpad = 4096                        cfgxdes = 16
cfgaffinitymask = 0                  cfgIOAffinityMask = 0                cfgbuf = 4362
cfgdes = 0                           cfglocks = 0                         cfgquerytime = 600
cfgcursorthrsh = -1                  cfgrmttimeout = 10                   cfg_dtc_rpcs = 0
cclkrate = 31250                     cfg_max_text_repl_size = 65536       cfgupddate = 40947
cfgupdtime = 22332689                fRemoteAccess = 1                    cfgbufminfree = 331
cnestedtriggers = 0x1                cdeflang = 0                         cfgTransformNoiseWords = 0x0
cfgPrecomputeRank = 0x0              crossdbownchain = 0                  cidCfg = 0x3400d008
cidCfgOld = 0x3400d008               cfgCutoffYear = 2049                 cfgLangNeutralFT = 1033
maxworkthreads = 0                   minworkthreads = 32                  minnetworkthreads = 32
threadtimeout = 15                   connectsperthread = 0                cusroptions = 0
exchcostthreshold = 5                maxdop = 0                           cpwdpolicyupgrade = 0x1
cfServerTriggerRecursion = 1         cfDisableResultsetsFromTriggers = 0  cfgPHConnectTimeout = 60
CLREnabled = 0                       cfgmaxcrawlrange = 4                 ftSmallBufMin = 0
ftSmallBufMax = 100                  ftLargeBufMin = 0                    ftLargeBufMax = 100
RemoteDacEnabled = 0                 CommCritComplianceEnabled = 0        EkmEnabled = 0
cUserInstanceTimeout = 0x3c          cfgEnableUserInstances = 0x1         m_BackupCompressionDefault = 0x0
FilestreamAccessLevel = 0            OptimizeForAdhocWorkloads = 0        cchecksum = 1787


DBCC execution completed. If DBCC printed error messages, contact your system administrator.