Ebben a cikkben az SQL Server Auto Shrink adatbázis tulajdonságát tárgyaljuk, amely lehetővé teszi, hogy az SQL Server automatikusan zsugorítsa az adatbázisfájlokat, ha az értéket True értékként konfiguráljuk az adatbázis-beállításban. Az automatikus zsugorítási műveletet a szerver automatikus zsugorítás adatbázis tulajdonsága végzi, amely a cikk fő kurzusa.
Az adat/naplófájlok zsugorítási tevékenységének elkerülése érdekében, amikor a fájl mérete meghaladja az adatfájlban lévő szabad helyet, a DBA-nak (adatbázis-adminisztrátor) rendszeres időközönként biztonsági másolatot kell készítenie a naplófájlokról. Az egész adatbázis biztonsági mentése nem jó ötlet, a tranzakciós naplót is meg kell építeni vagy be kell állítani. A tranzakciós naplók száma addig fog növekedni, amíg el nem foglalja az összes rendelkezésre álló lemezterületet, ha nem készít róluk biztonsági mentést. Ha biztonsági mentést készít az adatbázisról, az felszabadítja a szabad helyet, hogy újra felhasználhassa. Az adatbázis-adminisztrátornak be kell ütemeznie a tranzakciós napló biztonsági mentését, hogy a naplófájlok mérete megfelelő méretűre csökkenjen.
Az SQL Server automatikus zsugorítás funkciója alapértelmezés szerint le van tiltva az SQL Server-példányadatbázisokon. Abban a forgatókönyvben, amikor több viszonylag kis méretű adatbázis van, amelyek nagyobb méretűre nőnek vagy új tuplik beillesztésével, vagy több tuplik törlésével keletkező nagyszámú üres hely miatt, az SQL Server Auto Shrink (Automatikus zsugorítás) funkció nagyon hasznos lesz ebben a helyzetben. Ráadásul nem kell aggódnia az adatbázis-fájlok méretbeli töredezettsége miatt sem.
Az adatbázis zsugorítása során a karbantartás részeként figyelembe kell vennie a zsugorítási műveleteket mind a kritikus, mind a viszonylag nagyobb adatbázisokon. Emellett ezt el kell kerülni a manuális zsugorítás funkció futtatását; ennek az a következménye, hogy az új vagy meglévő kéréssel kapcsolatos problémákat soha nem ismeri meg. A tranzakciós fájlok zsugorítása azonban jobb, mint az adatfájlok zsugorítása.
Az SQL Server automatikus zsugorításának aktiválásának és deaktiválásának módja az adatbázis számára?
A felhasználók az SSMS és a T-SQL segítségével mindkét módon engedélyezhetik és letilthatják az adatbázis automatikus zsugorítási opcióját.
Az adatbázis automatikus zsugorításának aktiválása az SSMS használatával:
A felhasználók ezt az opciót az adatbázis tulajdonságaiból engedélyezhetik vagy tilthatják le, az Auto Shrink címkével. Itt a True a legördülő listában engedélyezi ezt az opciót az adatbázis számára.
Az adatbázis automatikus zsugorításának engedélyezése T-SQL használatával:
A felhasználók az alábbi T-SQL utasításokat hajthatják végre az adatbázis zsugorításának engedélyezéséhez vagy letiltásához. A fenti T-SQL utasításhoz az AdventureWorks adatbázist használtuk.
|
1
2
3
4
5
6
|
–Az automatikus zsugorítás engedélyezése az AdventureWorks adatbázisban
ALTER DATABASE AdventureWorks SET AUTO_SHRINK ON
GO
—Az AdventureWorks adatbázis automatikus zsugorításának kikapcsolása
ALTER DATABASE AdventureWorks SET AUTO_SHRINK OFF
GO
|
Az adatbázis zsugorításának hatása a lekérdezési teljesítményre
A lekérdezési teljesítmény oldalán rossz hatások merülhetnek fel, ha az adatbázis automatikus zsugorítási opcióját és az automatikus növekedés beállításait együtt kapcsolja be az adatbázishoz. Az adatbázis méretének optimális értékre való beállításával, vagy többnyire minden adatbázisnak van néhány paramétere, ahol az automatikus növekedési funkciók be vannak kapcsolva.
Az ilyen adatbázisok esetében az automatikus zsugorodási funkciókat akkor kell aktiválnunk, amikor az adatbázis kisebb, egy és nincs több CRUD művelet, így lehetővé teszi az adatfájlok zsugorodását és visszaszerezheti a szabad helyet, amit célzottan azért biztosítottunk, hogy távol tartsuk adatbázisainkat az automatikus növekedési eseményektől. A szabad hely automatikusan felszabadul az adatfájlokban és a naplófájlokban időszakonként körkörös szekvenciális prioritással az automatikus zsugorítás funkció által, ha több adatbázisban bekapcsolták ezt a funkciót.
Nagy méretű adatbázisok esetén az automatikus növekedés, majd ezt követően az automatikus zsugorítás végrehajtásra kerül, ami a rendszerszintű töredezettség által vezetett teljesítményproblémákat eredményez. Összefoglalva mindezeket, bármely adatbázis esetében az automatikus zsugorítást a következő okok alapján nem szabad aktiválni:
- Az SQL Server automatikus zsugorítási algoritmusainak minden cél nélküli végrehajtása minden bizonnyal hatalmas mennyiségben fogja pazarolni az erőforrásokat
- Akár az SQL Server automatikus zsugorítását, akár a kézi zsugorítást hajtja végre, nyilvánvalóan az index töredezettségét fogja okozni, és ez végül az adatfájlok zsugorodását is végrehajtja
- Ha a kiszolgáló az IO alrendszer határait is erőlteti, a zsugorítás futtatása túlnyomhatja azt, ami hosszú lemezes várólisták hosszát és esetleg IO timeoutokat eredményez, ez hatalmas mennyiségben emészti fel a szerver IO és CPU erőforrásait
- A rendszer teljesítményét hátráltatja a fájlrendszer lemezszintű töredezettsége, ami ismét a zsugorodó és növekvő adatfájlok gyakori elvégzésének közvetlen hatása
Ha többet szeretne megtudni az SQL Server növekedési és zsugorodási eseményeiről, olvassa el ezt a cikket: Get details of SQL Server Database Growth and Shrink Events.
A kritikus adatbázisok esetében a manuális zsugorítási műveletet az adatbázisfájlok szintjén futtathatja a szakértő. A kézi zsugorítási fájltevékenység akkor végezhető el, amikor a törlési művelet végrehajtásra kerül, és ezt követően a hely visszanyert. A zsugorítási művelet végrehajtásakor újra kell építenünk a töredezett indexeket, mivel a zsugorítási művelet indextöredezéshez vezethet. Az index töredezettségi százalékát a felhasználó a DMV-k T-SQL utasításainak használatával ellenőrizheti. A naplófájl zsugorítását azonban manuálisan kell elvégezni, amikor és amikor szükséges, és nem lehet a rendszeres karbantartási tevékenység része.
A lemezhasználat rendszeres nyomon követése érdekében a lemezhasználati jelentést a felhasználó a kézi zsugorítási művelet végrehajtása előtt elemezheti az SSMS segítségével, amely betekintést nyújt az adatbázis adat- és naplótér információiba a kijelzőn. Ha a felhasználó szeretne egy műszerfalat kapni az adatbázis-fájlok lefoglalt és szabad helyének kiszámításához, akkor a Lemezhasználati jelentés nagyon hasznos lesz. A lemezjelentés azonban az SQL Server DMV-k segítségével lakja az információkat. A lemezjelentés az SSMS használatával az alábbi könyvtárban érhető el:
Adatbázis >> Jelentések >> Standard jelentések >> Lemezhasználat
Itt van egy lemezjelentés az adatbázishoz. Az adatfájlok és naplófájlok valós idejű statisztikáit láthatja a felhasználó. Ez a jelentés tartalmazza az összes lefoglalt hely, az adatfájlok lefoglalt helye, a tranzakciós napló lefoglalt helye és a memórián belüli OLTP-foglaltság elsődleges adatait.
Az adatbázis automatikus zsugorítása SQL Server munkák használatával
Az ilyen szkripteket a felhasználó által ütemezett munkával lehetne végrehajtani, hogy az adatbázis zsugorítási műveletet ütemezői tevékenységgel hajtsa végre. A szkript első lépéseként keresse meg a szabad helyet az adatbázisfájlban, majd zsugorítsa a fájlt, ha a szabad hely meghatározott kritériumai megfelelnek. A szabad helyet az SQL Server DMV-k segítségével kell kiszámítani.
Alapvetően az adatbázisfájlok felügyeletét az adatbázis-adminisztrátor végzi, aki viszont az adatbázisban lépéseket tesz az adatbázisfájlok méretének felügyeletére. Ha az előre meghatározott határt bármely fájl átlépi, akkor ilyen lépéseket kell végrehajtaniuk. Tehát ez a tevékenység automatizálható SQL Server feladat segítségével, amelyet naponta, hetente vagy havonta, előre meghatározott időpontban kell elvégezni.
A legjobb gyakorlat az, ha ezt a feladatot csak az adatbázis naplófájljára ütemezzük, és az adatfájlok szabad helyét manuálisan figyeljük. Ez ugyanis hatással lehet a lekérdezések teljesítményére is. Az SQL Server DMV az adatfájlt és a naplófájlt ugyanabban az eredményhalmazban adja vissza, ezért a fájltípust az SQL Server DMV T-SQL utasításában ketté kell választani. A fájl zsugorítása előtt a felhasználó lekérdezési logikát alkalmazhat a naplófájl teljes és szabad helyére. Ha a számítási kritériumok megegyeznek a fájl tulajdonságával, akkor a fájl a célfájl méretével zsugorodik.
Az adatbázisfájlok szabad helyének ellenőrzése:
Ha ilyen kritériumok megegyeznek az adatbázisfájlokkal, például ha a szabad hely nagyobb, mint (n) MB/GB, ha n(%) szabad hely a teljes helyhez képest, és még sok más. Ha a szabad hely nem áll rendelkezésre a naplófájl számára, és a fájl mérete közel van a maximális fájlméret paraméterértékhez, akkor az adatbázis-adminisztrátornak keresnie kell a tranzakciós naplóban.
Az adatbázisfájl zsugorítása:
|
1
|
DBCC SHRINKFILE(file_name, 5120);
|
Itt az 5120 a célfájl mérete MB-ban. Tehát a fájl mérete 5120 MB lesz. Állítsa be a T-SQL lekérdezést a szükséges kritériumokkal az SQL Server feladatlépésben, és ütemezze be a be-ki órákra.
Az SQL Server automatikus zsugorítás opciója nem engedélyezhető minden adatbázis esetében. Konkrétan a kisebb adatbázisok esetében segít, amelyek viszonylag kevesebb CRUD műveletet végeznek.
Következtetés
Ebben a cikkben az SQL Server automatikus zsugorítás adatbázis tulajdonságát tárgyaltuk az adat- és naplófájlok zsugorításához és a kihasználatlan hely eltávolításához. Az adatbázis zsugorítása költséges művelet, és óvatosan kell használni.
- Author
- Recent Posts
Jignesh Raiyani összes bejegyzése
- Page Life Expectancy (PLE) in SQL Server – July 17, 2020
- Hogyan automatizálhatjuk a táblák particionálását az SQL Serverben – 2020. július 7.
- SQL Server Always On Availability Groups konfigurálása az AWS EC2-n – 2020. július 6.